Bạn đang xem: Sử dụng data-validation khi danh sách nguồn nằm trong một sheet khác
Tính năng này thường được thực hiện trong quy trình nhập liệu, đặc biệt quan trọng khi yêu cầu nhập các dãy số theo một quy tắc như thế nào đó. Chẳng hạn như:
Tỉ số là một trong những tự nhiên to hơn hoặc bằng và nhỏ tuổi hơn hoặc bằng 10.Số điện thoại cảm ứng có 10 cam kết tự số.Số minh chứng nhân dân bao gồm 9 chữ số.Số căn cước công dân có 12 chữ số…Nếu bạn nhập không nên (ví dụ điểm các bạn nhập là 11) thì ngay mau chóng sẽ xuất hiện hộp thoại thông báo, khi đó bạn nên sửa lại.
Chúng ta có tất cả 7 phép tắc nhập liệu vào Excel, kia là: Bất kỳ giá trị nào, Số nguyên, Số thập phân, Danh sách, Ngày, Giờ, Độ dài văn phiên bản Và Phong tục. khi menu là trong những quy tắc được áp dụng nhiều nhất.
Tuy nhiên, mẫu gì cũng đều có hai mặt của nó. Luật lệ nhập liệu này còn có hạn chế (đối với những phiên phiên bản Microsoft Excel cũ hơn) là cấp thiết sử dụng list nguồn nếu danh sách nằm vào một Trang tính khác.
//*đề tài*//
Được rồi, bây chừ hãy làm một ví dụ. Ví dụ bọn họ có một Workbook với 2 Worksheet mang tên lần lượt là một và 2.
Bài tập 1 địa điểm Xác thực dữ liệu được sử dụng.Bài tập 2 nơi list nguồn được giữ trữChủ đề là 1 yêu ước tạo Data Validation đến khối ô C3:C6 với phép tắc nhập liệu là List. Thay thể, các bạn chỉ hoàn toàn có thể chọn Huyện/ TP trực trực thuộc tỉnh gồm trong danh sách:
# Đầu tiên. Phương pháp 1: Đặt thương hiệu cho danh sách nguồn
+ bước 1: chắt lọc Worksheet 2 => chọn khối ô B2:B10 => đặt tên đến khối ô này bằng cách nhập tên ngẫu nhiên vào ô Name Box
Trong phạm vi nội dung bài viết này tôi đang đặt tên cho nó Huyen => sau đó nhấn phím Enter trên bàn phím.
+ cách 2: gạn lọc Worksheet 1 => lựa chọn khối ô C3:C6
+ cách 3: tuyển lựa Data => chọn tiếp sau Data Validation
+ cách 4: vỏ hộp thoại Data Validation mở ra như hình mặt dưới:
TRÊN Allow sự lựa chọn của khách hàng là Listđánh vệt Ignore blank và In-cell dropdownTRÊN Source bạn lấn sân vào =Huyen
=> Sau đó lựa chọn OK
Cuối thuộc ta được tác dụng như hình bên dưới:
#2. Giải pháp 2: thực hiện INDIRECT(ref_text, )
Mấu chốt của cách thức này là chúng ta sử dụng hàm INDIRECT(ref_text,
+ bước 1: sàng lọc Worksheet 1 => lựa chọn khối ô C3:C6
+ cách 2: tuyển lựa Data => lựa chọn Data Validation
+ bước 3: vỏ hộp thoại Data Validation quan sát thấy:
TRÊN cho phép bạn chọn phong cáchListđánh dấu Ignore blank và In-cell dropdown
TRÊN NGUỒN nhập khẩu =INDIRECT(“’2’!$B$2:$B$10”)
=> tiếp nối bạn lựa chọn OK
#3. Giải pháp 3: “Quét khối”
+ bước 1, cách 2, bước 3 làm tương tự như phương thức 2 mặt trên. Thay bởi Bước 3 gắng vì đi vào trong “quét khối” trực tiếp. Cách thức này có thể được áp dụng khi “khối quét” phía bên trong một khu vực nhỏ.
Trong hộp thoại Data Validation CHỌN
+ bước 4: chọn lựa Worksheet 2 => “quét khối” khối ô B2:B10 => chọn
#4. Phần kết
Okay, vậy là tôi đã hướng dẫn dứt cho các bạn cách áp dụng Data Validation khi source list nằm trên Sheet khác.
Phương pháp 1 (Đặt tên cho list nguồn) và cách thức 2 (Sử dụng GIÁN TIẾP(ref_text,
Phương pháp 3 (“Quét khối”) chỉ hoạt động với Microsoft Excel 2019 và những phiên phiên bản mới hơn.
Tôi không chắc hẳn liệu nó có vận động trong phiên bản 2013, năm 2016 hay không? Vì lúc này mình không thực hiện 2 phiên bạn dạng này nên nếu như khách hàng đang áp dụng phiên bản nào vào 2 phiên bạn dạng này thì nên cho bản thân biết ở đoạn bình luận dưới nhé.
Hy vọng bài viết này có ích cho bạn. Xin chào và hẹn gặp lại chúng ta trong những nội dung bài viết tiếp theo!
CTV: Nhựt Nguyễn – Blogchiasekienthuc.com
nội dung bài viết đạt được: 5/5 sao – (Có 3 tiến công giá)
Ghi chú: Bài báo này hữu dụng với bạn? Đừng quên đánh giá bài viết, say mê và chia sẻ nó với anh em và mái ấm gia đình của bạn!
bạn có thể giúp mọi fan làm việc công dụng hơn trong trang tính bằng phương pháp sử dụng list thả xuống trong các ô. Danh sách thả xuống có thể chấp nhận được mọi người lựa chọn một mục từ list mà các bạn tạo.
lựa chọn ô bạn có nhu cầu đặt list thả xuống vào trang tính.
Trên menu cài đặt trong vỏ hộp thoại có thể chấp nhận được , click chuột danh sách .
nhấp chuột nút mối cung cấp , rồi chọn phạm vi list của bạn. Cửa hàng chúng tôi đặt danh sách của mình trên một trang tính mang tên Thành phố, vào dải ô A2:A9. để ý rằng shop chúng tôi đã bỏ hàng tiêu đề vì không thích hàng này là 1 tùy chọn lựa chọn:
bấm vào nút lưu ý Lỗi Tab.
Nếu bạn có nhu cầu thông báo nhảy lên khi ai đó nhập dữ liệu không tồn tại trong list của bạn, hãy chọn nút Hiển thị chú ý lỗi sau thời điểm nhập dữ liệu không đúng theo lệ , chọn một tùy lựa chọn từ vỏ hộp phong cách cùng nhập tiêu đề và thông báo. Nếu khách hàng không mong mỏi hiển thị thông báo, hãy vứt chọn hộp kiểm.
Không chắc hẳn rằng nên chọn tùy lựa chọn nào trong danh sách phong cách Hộp?
Để hiện thông báo không ngăn cản mọi tín đồ nhập dữ liệu không có trong danh sách thả xuống, hãy bấm vào thông tin hoặc Cảnh báo. Thông tin sẽ hiển thị thông báo cùng với biểu tượng này
.
Để ngăn quán triệt mọi fan nhập dữ liệu không tồn tại trong list thả xuống, hãy nhấn vào giới hạn .
Lưu ý: nếu như khách hàng không thêm title hoặc văn bản, Excel sẽ sử dụng tiêu đề khoác định "Microsoft Excel" cùng thông báo: "Giá trị chúng ta nhập không phù hợp lệ. Một người dùng đã giới hạn các giá trị hoàn toàn có thể được nhập vào ô này."
Sau khi chúng ta tạo list thả xuống, hãy bảo đảm rằng nó vận động theo cách các bạn muốn. Ví dụ: bạn cũng có thể muốn kiểm tra xem đổi khác chiều rộng lớn cột và độ cao hàng nhằm hiển thị toàn bộ các mục nhập của bạn.
Nếu danh sách mục nhập của danh sách thả xuống nằm tại trang tính khác và bạn muốn ngăn không cho người dùng thấy được hoặc biến đổi nó, hãy suy nghĩ việc ẩn và bảo đảm an toàn trang tính đó. Để biết thêm tin tức về cách bảo đảm an toàn trang tính, hãy xem Khóa ô để đảm bảo chúng .
Để xóa list thả xuống, hãy xem sa thải danh sách thả xuống .
việc nhập tài liệu sẽ cấp tốc hơn và đúng mực hơn khi chúng ta hạn chế quý hiếm ô với phần đa lựa lựa chọn từ list thả xuống.
ban đầu bằng cách tạo danh sách các mục nhập vừa lòng lệ bên trên một trang tính, rồi thu xếp hoặc thu xếp lại những mục nhập để chúng lộ diện theo lắp thêm tự bạn muốn. Sau đó, bạn có thể sử dụng các mục nhập làm cho nguồn cho danh sách dữ liệu thả xuống. Nếu danh sách không lớn, chúng ta cũng có thể dễ dàng tìm hiểu thêm danh sách cùng nhập các mục nhập trực tiếp vào công cụ xác nhận tính thích hợp lệ dữ liệu.
Để tạo danh sách mục nhập hợp lệ cho danh sách thả xuống, hãy nhập nội dung vào trong 1 cột hoặc một mặt hàng không cất ô trống trong trang tính.
chọn ô bạn muốn hạn chế dữ liệu nhập vào.
click chuột nút thiết đặt rồi trong hộp thoại có thể chấp nhận được menu bật lên, nhấn vào danh sách .
bấm vào nút mối cung cấp , rồi bên trên trang tính, lựa chọn danh sách các mục nhập thích hợp lệ của bạn.
hộp thoại vẫn thu nhỏ cực tiểu để giúp bạn coi trang tính dễ dàng hơn.
nhấn RETURN hoặc bấm chuột nút mở rộng
để phục hồi hộp thoại, rồi click chuột OK .
Mẹo:
bạn có thể nhập trực tiếp giá trị vào mối cung cấp , được phân tách bằng dấu phẩy.
Để sửa thay đổi danh sách những mục nhập hòa hợp lệ, chỉ cần đổi khác các quý giá trong danh sách nguồn hoặc chỉnh sửa dải ô vào nguồn Hộp.
bạn có thể chỉ định thông tin lỗi của riêng bản thân để đánh giá cho tài liệu đầu vào không phù hợp lệ. Trên menu tài liệu , nhấn vào Xác thực tài liệu Hoặc xác thực , rồi nhấp chuột nút chú ý Lỗi Tab.
Áp dụng xác nhận tính thích hợp lệ dữ liệu so với ô
trong trang tính mới, nhập hồ hết mục nhập bạn có nhu cầu có trong danh sách thả xuống của mình. Rất tốt là chúng ta nên đặt những mục list trong một Bảng Excel .
Lưu ý:
chọn ô bạn có nhu cầu đặt danh sách thả xuống trong trang tính.
Đi tới trang dữ liệu trên Dải băng, rồi click chuột Xác thực dữ liệu .
Trên thực đơn thiết lập trong vỏ hộp thoại cho phép , nhấn vào list .
nếu như khách hàng đã tạo ra bảng có các mục nhập thả xuống, hãy bấm chuột nút nguồn , rồi bấm và kéo những ô có chứa mọi mục nhập đó. Mặc dù nhiên, đừng gộp cả ô tiêu đề. Chỉ gửi vào các ô sẽ xuất hiện thêm trong danh sách thả xuống. Bạn có thể chỉ buộc phải nhập danh sách các mục nhập trong nguồn được phân tách bóc bởi dấu phẩy như sau:
Trái cây,Rau,Ngũ cốc,Sản phẩm bơ sữa,Đồ ăn nhẹ
ví như mọi bạn để trống ô, nên lựa chọn nút bỏ qua mất trống Hộp.
bình chọn list thả xuống vào ô Hộp.
bấm chuột nút thông báo Nhập Tab.
Nếu bạn có nhu cầu thông báo nhảy lên khi nhấn vào ô, nên chọn lựa nút hiển thị thông báo cùng nhập tiêu đề cùng thư vào các hộp (tối đa 225 ký kết tự). Nếu khách hàng không ý muốn hiển thị thông báo, hãy quăng quật chọn vỏ hộp kiểm.
bấm vào nút lưu ý Lỗi Tab.
Nếu bạn muốn thông báo bật lên lúc ai kia nhập dữ liệu không có trong danh sách của bạn, nên chọn nút Hiện cảnh báo , lựa chọn 1 tùy chọn trong các loại , rồi nhập tiêu đề và thông điệp. Nếu khách hàng không mong mỏi hiển thị thông báo, hãy bỏ chọn vỏ hộp kiểm.
Nhấp vào OK .
bạn cần thêm trợ giúp?
Bạn luôn hoàn toàn có thể hỏi một chuyên gia trong cộng đồng Kỹ thuật Excel hoặc thừa nhận sự cung cấp trong
Cộng đồng trả lời.Bạn đã xem: thực hiện data-validation khi list nguồn nằm trong một sheet khác
Chức năng danh sách lựa lựa chọn (list) vào Excel rất thân thuộc với các người. Mặc dù nhiên, khôn xiết ít người biết cách sử dụng Data Validation rượu cồn để tạo list phụ thuộc vào vào một danh mục khác.
Bài viết sau đây sẽ hướng dẫn cụ thể cách chế tạo ra list phụ thuộc vào vào danh mục khác một giải pháp nhanh chóng.
Ở phía trên ta bao gồm danh sách các tỉnh thuộc tên các bệnh viện ở trong tỉnh đó. Yêu cầu đặt ra tạo ra 2 danh sách mà lúc ta lựa chọn tỉnh ngơi nghỉ danh sách đầu tiên thì danh sách thứ nhì sẽ mang lại ra các bệnh viện của tỉnh kia tương ứng.
Lưu ý, một tỉnh có khá nhiều các khám đa khoa và list này đề nghị được thu xếp (sorted) để bảo đảm an toàn các cực hiếm giống nhau (tỉnh) nằm kế nhau kèm với tên bệnh viện.
Để tạo nên danh sách phụ thân (parent list) chuẩn là tên các tỉnh/thành phố không xẩy ra trùng thì ta đề xuất copy toàn bộ tên tỉnh/thành phố ra 1 sheet khác với sử dụng tác dụng “bỏ cực hiếm trùng” của Excel (Data -> Remove duplicates) , tạo thành tên những tỉnh ko trùng tự nguồn dữ liệu thô.
Ở trên đây ta đã tất cả một danh sách cha (parent list) có tên các tỉnh, sử dụng tác dụng List trong Data Validation mà các bạn đã biết mà lại mình sẽ không còn nhắc lại bí quyết làm nữa mà tập trung hướng dẫn cách link hai danh sách cha-con lại với nhau.
Để thực hiện có hai cách, là sử dụng Range Name để gán vùng dữ liệu các bệnh viện cho tỉnh kia hoặc là sử dụng công thức, tại chỗ này mình đã dùng phương pháp dùng bí quyết còn Range Name sẽ trình làng ở một nội dung bài viết khác.
Công thức như sau: Ở ô E3, lựa chọn Tab Data -> Data Validation, bảng tác dụng hiện lên, mục Source , gõ công thức:
=OFFSET(‘Benh vien’!$A$2,MATCH($C$3,’Benh vien’!$A:$A,0)-2,1,COUNTIF(‘Benh vien’!$A:$A,$C$3),1)
=OFFSET(reference, rows, cols, , )
HÀM OFFSET
Hàm này để xác định một vùng linh hoạt, đổi khác tùy trực thuộc vào các dòng cùng cột được xác định.
Trong lấy ví dụ như trên, hàm OFFSET sẽ giúp đỡ bạn tự động hóa giới hạn và update Danh sách gọn ghẽ “Bệnh viện ở trong tỉnh” nếu như khách hàng đã chọn “Tỉnh”.
+ Reference: Điểm bắt đầu của vùng tài liệu , bọn chúng ta bước đầu từ ô A2 vào sheet dịch viện
+ Rows: sử dụng hàm MATCH để tìm dòng trước tiên của thức giấc được chọn lọc ở danh sách tỉnh (danh sách cha) . Ô C3 được lựa chọn cất tên tỉnh đề xuất tìm kiếm danh sách bệnh viện. Cách làm này nhằm tìm kiếm dòng thứ nhất cho ra kết quả tương xứng với tỉnh được lựa chọn. Trừ đi 2 cho một ô trống A1 và tiêu đề A2.
Xem thêm: Thủ Tục Làm Hộ Chiếu Cho Trẻ Em 2017, Thủ Tục Liên Quan Đến Hộ Chiếu
+ Cols: Ghi quý giá “1” để dịch rời một cột qua mặt phải. Công thức tìm kiếm với lấy những giá trị của cột B ( tên dịch viện) tương xứng với cột A (tên tỉnh)
+ Heights: sử dụng COUNTIF nhằm đếm số lần xuất hiện của thương hiệu tỉnh trong list nguồn . Ví dụ: tỉnh giấc YENBAI trả về 4, nghĩa là vùng dữ liệu sẽ là cao 4 dòng,
+ Width: Vùng tài liệu rộng 1 cột (cột B)
Với lý giải trên chúng ta cũng có thể áp dụng cho cân xứng với sệt thù công việc của mình.
Khóa học thực hành Excel mang lại Kiểm toán Khóa học tập Dashboard ReportingKhóa học VBA