Thông báo:
Do blogspot bị chặn bởi VNPT, nên mình sẽ không sử dụng blogspot nữa, thay vào đó là sử dụng website sau:
https://sites.google.com/site/takeyourcel/
Mong các bạn vui lòng ghé qua.
Thân!
Bài 4: Sử dụng chức năng đặt tên (Define Name)- Giúp dễ nhớ các vùng, bảng dữ liệu - dễ quản lý dữ liệu và áp dụng vào công thức.
- Giúp công thức ngắn gọn dễ hiểu, dễ sửa chữa - chỉ cần thay đổi nội dung trong vùng dữ liệu thì kết quả công thức sẽ thay đổi theo mà không phải sửa lại công thức.
- Thực hiện một số công thức phức tạp, hoặc với số lượng lớn công thức cần xử lý.
- Làm đơn giản hóa excel, gồm các hàm, hoặc các công thức phức tạp.
- Tránh sai sót, nhầm lẫn trong việc quản lý dữ liệu hay trong việc lập công thức.
2. ĐẶT TÊN VÙNG
Chọn ô, dãy ô mà bạn muốn đặt tên.
Cách 1: Nhấp hộp Name ở bên trái ngoài cùng của thanh công thức rồi gõ nhập tên muốn đặt. Nhấn Enter
Cách 2: Đặt tên thông qua Insert/Name/Define hay dùng tổ hợp phím tắt Ctrl+F3, hôp thoại Name Define hiện ra (trong excel 2010 là hộp thoại Name manager, chọn New... -> ra hộp thoại New name), Trong Names in workbook bạn gõ tên muốn đặt, trong Refers to : bạn gõ vùng tham chiếu. Nếu đặt tên cho một hằng hay một công thức thì hộp thoại Refers to bạn sẽ gõ hằng hay công thức vào đây. (Nếu bạn đã chọn vùng mà muốn đặt tên trước thì vùng đó sẽ xuất hiện sẵn trong Refers to)
Cách 3: Đặt nhãn thông qua hộp thoại Insert/Name/Label. Thường được sử dụng để đặt nhãn cho một cột có tiêu đề, chọn từng cột luôn cả tiêu đề, add vào hộp thoại Add Label Range/Add/Ok. Nhãn của vùng sẽ lấy theo tiêu đề cột.
Hình 1: Giao diện Name Manager
Hình 2: Giao diện Define Name (New Name)
Những qui định về việc đặt tên cho các ô, các công thức, các hằng :
1. Các ký tự được phép : Ký tự đầu tiên của 1 tên phải là mẫu tự, hoặc ký tự gạch dưới. Các ký tự còn lại trong tên có thể là các mẫu tự, các số, các dấu chấm, và các ký tự gạch dưới. Ví dụ : _01_01_04 hay HH_01
2. Các tên không thể là một tham chiếu ô . Td : R1C1 hay A$1
3. Có thể dùng nhiều từ, nhưng không có khoảng trống. Các ký tự gạch dưới hay dấu chấm có thể được dùng để nối từ. Ví dụ: Product_01 hay Product.01
4. Số ký tự : Một tên có thể lên đến 255 ký tự
5. Loại kiểu chữ dùng trong tên : Excel không phân biệt ký tự in hoa và ký tự thường trong các tên
Sử dụng các nhãn và tên :
Các sheet thường có các nhãn ở đỉnh của mỗi cột và phía bên trái của mỗi hàng nhằm mô tả dữ liệu ở bên trong của nó. Bạn có thể sử dụng các nhãn này trong các công thức lúc bạn muốn tham chiếu đến các dữ liệu liên quan. Bạn cũng có thể đặt các tên mô tả để dễ gợi nhớ cho các Cell, dãy các Cell, các công thức hay các hằng.
Lưu ý : Excel không nhận ra các nhãn trong công thức nên bạn hãy vào Tools/Options/Calculation. Bên trong Workbook options bạn hãy chọn hộp kiểm Accept labels in formulas
| Nội dung | Excel 2003 | Excel 2007 - 2013 |
| Xóa tên | Vào mục Insert/Name/Define, click chọn tên vùng đã đặt mà muốn xóa bỏ, rồi chọn Delete (bên phải) | Ấn Ctrl+F3 (Mở hộp thoại Name manager), trong hộp thoại Name manager, chọn tên muốn xóa bỏ rồi ấn Delete |
| Sửa tên | Xóa tên cũ, thực hiện lại thao tác đặt tên rồi đặt tên mới | Xóa tên cũ, thực hiện lại thao tác đặt tên rồi đặt tên mới. Hoặc trong hộp thoại Name manager chọn Edit rồi sửa tên vùng đó. |
| Thay đổi kích thước vùng đã đặt tên | Chọn vùng có kích thước mới (bôi đen vùng đó), sau đó đặt tên vùng này với đúng tên đã đặt của vùng cũ. | Ấn Ctrl+F3 (Mở hộp thoại Name manager), trong hộp thoại Name manager, chọn tên vùng muốn thay đổi kích thước, ấn Edit rồi chọn vùng cần thay đổi, ấn ok. (Kích thước vùng cần đặt tên nằm trong mục Refers to - nằm phía dưới của bảng Name manager. Thay đổi tọa độ trong mục này sẽ thay đổi kích thước của vùng) |
Bài viết bởi handung107 diễn đàn Giải pháp excel (GPE)
link gốc: http://www.giaiphapexcel.com/forum/showthread.php?7-T%C3%AAn-v%C3%A0-nh%C3%A3n-trong-c%C3%B4ng-th%E1%BB%A9c
3. Một số ứng dụng trong sử dụng tên vùng
Ví dụ: Bạn có 1 danh mục hàng hóa, gồm 2 cột Mã và Tên (từ A1:B6).
Mã Tên
BUT01 Bút bi Thiên Long
BUT02 Bút chì 2B
VO01 Vờ viết học sinh
SACH1 Sách toán
SACH2 Sách văn
Bạn đang có nhu cầu: khi bạn nhập mã vào 1 ô, thì ô bên cạnh sẽ hiện tên đầy đủ của hàng hóa đó.
Ví dụ tại ô D2 bạn nhập mã BUT01 - bạn muốn kết quả tại ô E2 là Bút bi Thiên Long.
Bạn sẽ dụng hàm vlookup như sau: Tại ô E2 bạn nhập =vlookup($D$2,$A$1:$B$6,2,0)
nhìn vào công thức trên, bạn sẽ thấy địa chỉ làm căn cứ tham chiếu cho hàm được thể hiện bằng tọa độ.
Như vậy sẽ khó khăn cho bạn khi muốn kiểm tra xem dữ liệu tham chiếu ở đâu, lại có nhiều ký hiệu tọa độ khiến bạn khó nhớ, khó sử dụng công thức.
Nếu như phải làm việc nhiều với bảng danh mục hàng hóa kia, mà nội dung trong bảng lại rất nhiều (hàng, cột) thì bạn làm thế nào cho nhanh, dễ nhớ, dễ kiểm tra được? Công thức ngắn gọn hơn, dùng nhanh hơn, dễ hiểu hơn?
Ta hãy áp dụng đặt tên vùng như sau:
Bôi đen vùng A1:B6 , đặt tên vùng là DanhMuc (theo quy ước đặt tên ko phân biệt chữ hoa, chữ thường. Do đó đặt chữ hoa xen lẫn chỉ để dễ phân biệt các từ)
Đặt tên ô D2 là GiaTri
Khi đó bạn tạo hàm vlookup là =vlookup(GiaTri,DanhMuc,2,0)
Hàm cho kết quả tương tự như hàm đã tạo ở trên, nhưng bạn dễ nhớ hơn và sử dụng nhanh hơn.
Ví dụ trên có thể khiến bạn chưa thực sự thấy được sự nhanh hơn, dễ làm hơn thông qua việc đặt tên vì bảng danh mục nhỏ, lại ít nội dung, yêu cầu còn ít. Vậy thử với nội dung lớn hơn xem sao
1 2 3 4 5
Mã Tên Nhà Sản xuất Giá nhập Chi tiết
BUT01 Bút bi Thiên Long Cty Thiên Long 2000 Màu xanh
BUT02 Bút chì 2B Cty Hồng Hà 3000 Màu xám
VO01 Vở viết học sinh Cty Hồng Hà 5000 Bìa xanh
SACH1 Sách toán Cty Minh Đức 9000 Lớp 9
SACH2 Sách văn Cty Minh Đức 8000 Lớp 9
...
SACH100 Sách sử Cty Minh Đức 8000 Lớp 9
Với 1 bảng số liệu gồm 5 cột, 101 hàng, tại sheet 1
Ở sheet 2, bạn cần làm các yêu cầu
Tại ô A2, bạn nhập 1 mã hàng, dùng công thức để xác định Tên (ô B2), Nhà sản xuất (C2), Giá nhập (D2), Chi tiết (E2)
Như vậy bạn cần làm tối thiểu 4 hàm vlookup cho 4 ô.
Nếu nhập công thức ko dùng tên vùng, ta sẽ thấy B2 = vlookup($A$2,Sheet1!$A$1:$B$101,2,0) , và nếu quên đặt dấu giá trị tuyệt đối, bạn sẽ bị sai kết quả khi sao chép công thức từ ô B2 sang C2, D2, E2
Tất nhiên bạn phải sửa giá trị 2 ở công thức B2 thành 3 khi sang ô C2 (tham chiếu tại cột thứ 3 - tìm hiểu kỹ hàm vlookup), thành 4 khi sang ô D2
Như vậy rất khó theo dõi nội dung hàm, cũng như cần chú ý nhiều hơn khi lập hàm.
Nếu dùng tên vùng thì sao:
Đặt tên vùng tại sheet 1 từ A1:B101 là DanhMuc
Đặt tên vùng tại sheet 2 ô A2 là GiaTri
Hàm vlookup sẽ là vlookup(GiaTri,DanhMuc,2,0)
Bạn ko cần phải nhớ dấu giá trị tuyệt đối cho tham chiếu vùng, cho ô giá trị, vì khi đặt tên vùng nó đã tự tham chiếu bằng giá trị tuyệt đối cho vùng được đặt tên.
Chưa kể 1 lợi ích khác: Khi bạn thêm / bớt ô, cột, hàng trong vùng DanhMuc thì sao.
Công thức dạng tham chiếu tuyệt đối vlookup($A$2,Sheet1!$A$1:$B$101,2,0) không tự thay đổi khi DanhMuc thay đổi. Các giá trị tham chiếu giữ nguyên. Do đó phần thêm/bớt có thể ko được tính hoặc tính sai
Công thức dạng tham chiếu tên vlookup(GiaTri,DanhMuc,2,0) sẽ tự thay đổi khi nội dung của vùng đặt tên được thay đổi. Nếu nội dung trong DanhMuc tăng/ giảm thì DanhMuc cũng thay đổi theo. Do đó nó được dùng để tính toán.
Một tác dụng nữa của tên vùng là thay thế công thức làm đơn giản hóa công thức
Theo ví dụ trên, có yêu cầu là tại sheet 2, ô A2 cho mã, ô B2 cho số lượng, tại C2 yêu cầu tính trực tiếp ra kết quả thành tiền. Bạn sẽ đặt công thức là C2 =B2*(vlookup(GiaTri,DanhMuc,2,0))
Để đơn giản công thức trên, ta tạo 1 tên là DonGia. Bạn vào Define Name, tại bảng New Name, mục Name nhập DonGia, mục Refer to bạn nhập =vlookup(GiaTri,DanhMuc,2,0) rồi ấn ok
Khi đó công thức C2 có thể đơn giản hóa là =B2*DonGia
Việc áp dụng linh hoạt tên vùng vào công thức tính mang lại rất nhiều lợi ích, cũng như thú vị hơn khi bạn sử dụng excel, giúp đem lại cảm giác gần gũi, linh hoạt, đẹp mắt hơn. Ta không thấy các công thức khô khan với các tọa độ, dài dòng khó hiểu với các công thức ghép nối chằng chịt. Mọi cái đơn giản hơn rất nhiều, dễ tìm, dễ nhớ, dễ sử dụng không chỉ cho người viết ra mà còn cho cả người xem.
Cái tên nói lên tất cả, và chỉ cần thông qua 1 bước đặt tên, bạn đã dễ dàng kiểm soát được những điều tưởng như rất rắc rối, khô khan và khó khăn.
3. Một số ứng dụng trong sử dụng tên vùng
Ví dụ: Bạn có 1 danh mục hàng hóa, gồm 2 cột Mã và Tên (từ A1:B6).
Mã Tên
BUT01 Bút bi Thiên Long
BUT02 Bút chì 2B
VO01 Vờ viết học sinh
SACH1 Sách toán
SACH2 Sách văn
Bạn đang có nhu cầu: khi bạn nhập mã vào 1 ô, thì ô bên cạnh sẽ hiện tên đầy đủ của hàng hóa đó.
Ví dụ tại ô D2 bạn nhập mã BUT01 - bạn muốn kết quả tại ô E2 là Bút bi Thiên Long.
Bạn sẽ dụng hàm vlookup như sau: Tại ô E2 bạn nhập =vlookup($D$2,$A$1:$B$6,2,0)
nhìn vào công thức trên, bạn sẽ thấy địa chỉ làm căn cứ tham chiếu cho hàm được thể hiện bằng tọa độ.
Như vậy sẽ khó khăn cho bạn khi muốn kiểm tra xem dữ liệu tham chiếu ở đâu, lại có nhiều ký hiệu tọa độ khiến bạn khó nhớ, khó sử dụng công thức.
Nếu như phải làm việc nhiều với bảng danh mục hàng hóa kia, mà nội dung trong bảng lại rất nhiều (hàng, cột) thì bạn làm thế nào cho nhanh, dễ nhớ, dễ kiểm tra được? Công thức ngắn gọn hơn, dùng nhanh hơn, dễ hiểu hơn?
Ta hãy áp dụng đặt tên vùng như sau:
Bôi đen vùng A1:B6 , đặt tên vùng là DanhMuc (theo quy ước đặt tên ko phân biệt chữ hoa, chữ thường. Do đó đặt chữ hoa xen lẫn chỉ để dễ phân biệt các từ)
Đặt tên ô D2 là GiaTri
Khi đó bạn tạo hàm vlookup là =vlookup(GiaTri,DanhMuc,2,0)
Hàm cho kết quả tương tự như hàm đã tạo ở trên, nhưng bạn dễ nhớ hơn và sử dụng nhanh hơn.
Ví dụ trên có thể khiến bạn chưa thực sự thấy được sự nhanh hơn, dễ làm hơn thông qua việc đặt tên vì bảng danh mục nhỏ, lại ít nội dung, yêu cầu còn ít. Vậy thử với nội dung lớn hơn xem sao
1 2 3 4 5
Mã Tên Nhà Sản xuất Giá nhập Chi tiết
BUT01 Bút bi Thiên Long Cty Thiên Long 2000 Màu xanh
BUT02 Bút chì 2B Cty Hồng Hà 3000 Màu xám
VO01 Vở viết học sinh Cty Hồng Hà 5000 Bìa xanh
SACH1 Sách toán Cty Minh Đức 9000 Lớp 9
SACH2 Sách văn Cty Minh Đức 8000 Lớp 9
...
SACH100 Sách sử Cty Minh Đức 8000 Lớp 9
Với 1 bảng số liệu gồm 5 cột, 101 hàng, tại sheet 1
Ở sheet 2, bạn cần làm các yêu cầu
Tại ô A2, bạn nhập 1 mã hàng, dùng công thức để xác định Tên (ô B2), Nhà sản xuất (C2), Giá nhập (D2), Chi tiết (E2)
Như vậy bạn cần làm tối thiểu 4 hàm vlookup cho 4 ô.
Nếu nhập công thức ko dùng tên vùng, ta sẽ thấy B2 = vlookup($A$2,Sheet1!$A$1:$B$101,2,0) , và nếu quên đặt dấu giá trị tuyệt đối, bạn sẽ bị sai kết quả khi sao chép công thức từ ô B2 sang C2, D2, E2
Tất nhiên bạn phải sửa giá trị 2 ở công thức B2 thành 3 khi sang ô C2 (tham chiếu tại cột thứ 3 - tìm hiểu kỹ hàm vlookup), thành 4 khi sang ô D2
Như vậy rất khó theo dõi nội dung hàm, cũng như cần chú ý nhiều hơn khi lập hàm.
Nếu dùng tên vùng thì sao:
Đặt tên vùng tại sheet 1 từ A1:B101 là DanhMuc
Đặt tên vùng tại sheet 2 ô A2 là GiaTri
Hàm vlookup sẽ là vlookup(GiaTri,DanhMuc,2,0)
Bạn ko cần phải nhớ dấu giá trị tuyệt đối cho tham chiếu vùng, cho ô giá trị, vì khi đặt tên vùng nó đã tự tham chiếu bằng giá trị tuyệt đối cho vùng được đặt tên.
Chưa kể 1 lợi ích khác: Khi bạn thêm / bớt ô, cột, hàng trong vùng DanhMuc thì sao.
Công thức dạng tham chiếu tuyệt đối vlookup($A$2,Sheet1!$A$1:$B$101,2,0) không tự thay đổi khi DanhMuc thay đổi. Các giá trị tham chiếu giữ nguyên. Do đó phần thêm/bớt có thể ko được tính hoặc tính sai
Công thức dạng tham chiếu tên vlookup(GiaTri,DanhMuc,2,0) sẽ tự thay đổi khi nội dung của vùng đặt tên được thay đổi. Nếu nội dung trong DanhMuc tăng/ giảm thì DanhMuc cũng thay đổi theo. Do đó nó được dùng để tính toán.
Một tác dụng nữa của tên vùng là thay thế công thức làm đơn giản hóa công thức
Theo ví dụ trên, có yêu cầu là tại sheet 2, ô A2 cho mã, ô B2 cho số lượng, tại C2 yêu cầu tính trực tiếp ra kết quả thành tiền. Bạn sẽ đặt công thức là C2 =B2*(vlookup(GiaTri,DanhMuc,2,0))
Để đơn giản công thức trên, ta tạo 1 tên là DonGia. Bạn vào Define Name, tại bảng New Name, mục Name nhập DonGia, mục Refer to bạn nhập =vlookup(GiaTri,DanhMuc,2,0) rồi ấn ok
Khi đó công thức C2 có thể đơn giản hóa là =B2*DonGia
Việc áp dụng linh hoạt tên vùng vào công thức tính mang lại rất nhiều lợi ích, cũng như thú vị hơn khi bạn sử dụng excel, giúp đem lại cảm giác gần gũi, linh hoạt, đẹp mắt hơn. Ta không thấy các công thức khô khan với các tọa độ, dài dòng khó hiểu với các công thức ghép nối chằng chịt. Mọi cái đơn giản hơn rất nhiều, dễ tìm, dễ nhớ, dễ sử dụng không chỉ cho người viết ra mà còn cho cả người xem.
Cái tên nói lên tất cả, và chỉ cần thông qua 1 bước đặt tên, bạn đã dễ dàng kiểm soát được những điều tưởng như rất rắc rối, khô khan và khó khăn.
Bài viết thực hiện bởi: Dương Quân
