Cách tạo gạn lọc trong Excel ko khó. Nội dung bài viết sẽ hướng dẫn chúng ta cách tạo menu trong Excel đơn giản dễ dàng nhất.
Bạn đang xem: Cách tạo ô chọn trong excel
Microsoft Excel là 1 trong những trong số phần mềm bảng tính thông dụng nhất hiện nay. Nó phía bên trong bộ sản phẩm văn chống Office của Microsoft. Tự lâu, Excel danh tiếng là 1 phần mềm có chức năng tính toán dữ liệu nhiều con số, sắp xếp bảng linh hoạt, dễ dàng dàng chia sẻ dữ liệu cùng nhiều hơn thế nữa nữa.
Không khó khăn tìm thấy nguyên lý xử lý dữ liệu bạn có nhu cầu trên thanh chế độ của Excel từ bỏ đự rượu cồn định dạng tài liệu theo máy tự đồ vật ngày tháng, tăng tốt thấp dần tới các hàm tính logic. Cũng chính vì thế, mặc dù hiện có khá nhiều phần mềm giống như như Excel mà lại nó vẫn không còn mất đi vị trí ứng dụng bảng tính sản phẩm đầu.
Nếu tiếp tục dùng Excel, ắt hẳn song lần bạn cũng muốn tự tay tạo danh sách thả xuống tuyệt drop menu trong Excel. Nghe bao gồm vẻ phức hợp nhưng thực tiễn rất 1-1 giản. Bạn có khá nhiều cách khác nhau để gia công việc này.Bưới đó là những cách tạo sàng lọc trong Excel đơn giản, dễ tuân theo nhất.
Tạo list có tác dụng hạn chế con số dữ liệu mục nhập vào để tránh lỗi chủ yếu tả hay đánh máy. Với Excel, bạn dễ dàng tạo những danh sách thả xuống cho các tùy lựa chọn khác nhau, chẳng hạn như giới tính, độ tuổi, chức vụ,… chú ý chung, bạn chỉ cần tạo drop danh mục cho một ô khi có sẵn những lựa chọn ví dụ cho ô đó. Tiếp sau đây là cách tạo drop menu trong Excel.
Cách tạo drop list trong Excel
Cách tạo list thả xuống trong ExcelCác tùy chọn danh sách thả xuống nâng cao
Cách tạo list thả xuống vào Excel
Quá trình tạo danh sách thả xuống vào Microsoft Excel rất đối kháng giản. Phiên bản chỉ đề xuất làm thêm các bước cơ bạn dạng sau:
Tạo Named Range: Đây là bước cần thiết để tạo list dùng trong drop list.Thêm Data Validation: cung ứng dữ liệu đến ô.Thêm Named Range vào Data Validation: Tập vừa lòng hai cách trên và xác minh nguồn cho danh sách thả xuống.Thiết lập thông tin nhập mang lại Data Validation: ko bắt buộc. Nó được cho phép bạn thêm một thông báo popup để hướng dẫn tín đồ đọc sử dụng bảng tính.Bây giờ, bọn họ hãy thuộc tìm hiểu cụ thể các bước:
1. Tạo ra một Named Range
Một phương pháp để tạo danh sách những mục cho danh sách xổ xuống thực hiện Data Validation là thêm danh sách của công ty vào một bảng tính và đặt tên hàng ô chứa danh sách. Bạn cũng có thể thêm list vào và một bảng tính khu vực bạn chuẩn bị thêm list xổ xuống hoặc một bảng tính khác. áp dụng một dãy ô chọn cái tên trong một drop list tạo cho nó dễ dàng bảo trì hơn.
Chúng ta sẽ khởi tạo danh sách thả xuống chứa một vài loại đồ ăn trong Sheet 2 như 1 ví dụ. Nhập từng mục vào một ô lẻ tẻ trong một cột hoặc một dòng. Chọn những mục, nhập tên đến dãy của ô vẫn được chọn trong Name Box (Tên) với nhấn Enter.
2. Thêm Data Validation
Đi cho tới bảng tính, nơi bạn muốn thêm list xổ xuống. Click vào tab Data > Data
Validation trong mục Data Tools.
Hãy bảo vệ rằng tab Settings vẫn kích hoạt trong hộp thoại Data Validation.
3. Thêm Named Range vào Data Validation
Sau đó, chọn List từ danh sách thả xuống Allow. Chúng ta sẽ sử dụng tên từ hàng đã được đặt tên của ô mà chúng ta đã xác minh để điền vào list thả xuống. Nhập văn bản sau trên đây vào hộp Source:
=Food
Thay cụ Food với bất kể tên nào bạn có nhu cầu và click vào OK.
Tùy chọn Ignore blank được tích theo mặc định. Điều này được cho phép bạn lựa chọn ô và bỏ chọn ô mà không cần chọn một mục. Nếu bạn có nhu cầu yêu mong một tùy chọn lựa được chọn từ danh sách thả xuống thì nên bỏ lốt tích cạnh bên nó.
4. Đặt tin nhắn cho Data Validation
Nếu bạn muốn hiển thị một tin nhắn popup khi ô chứa danh sách thả xuống được chọn, bạn click vào tab Input Message. Lựa chọn Show đầu vào message when the cell is selected và điền Title cùng Input message. Bạn cũng có thể sử dụng tab Error Alert nhằm thêm lời nhắn hiển thị khi nguồn vào được nhập chưa phù hợp lệ trong danh sách xổ xuống (chẳng hạn như ai đó nhập chữ thay bởi vì lựa chọn). Tích vào Show Error alert after invalid data is entered. Chọn Style cùng điền Title với Error message. Sau đó, nhấp vào OK.
Khi bạn chọn một ô chứa list thả xuống, mũi tên phía xuống bên dưới để chọn một lựa chọn hiển thị đã hiển thị sinh sống bên đề nghị ô. Nút mũi tên xuống chỉ hiển thị lúc ô được chọn. Hiện thời chúng ta vẫn tìm hiểu phương pháp để hiển thị mũi tên xuống bên cạnh danh sách thả xuống đa số lúc sau trong bài viết này.
Các tùy chọn list thả xuống nâng cao
Bây giờ, bạn đã sở hữu một list thả xuống cơ bản, hãy thường xuyên với các cài đặt nâng cao để chỉnh sửa và tùy chỉnh cấu hình danh sách của mình.
1. Chỉnh sửa hoặc xóa một Named Range
Nếu chúng ta cần sửa đổi hoặc xóa một dãy đã có đặt tên, chúng ta phải áp dụng Name Manager. Click vào tab Formulas > Name Manager vào phần Defined Names.
Để đổi khác phạm vi ô cho một tên trong hộp thoại Name Manager, các bạn chọn Name trong danh sách và click vào nút phạm vi ô ở cuối hộp thoại. Sau đó, chọn dải ô cùng nhấp vào nút phạm vi ô một đợt nữa trên phiên phiên bản nhỏ gọn gàng của vỏ hộp thoại Name Manager hệt như hình ảnh ở trên.
Tiếp theo, bạn click vào dấu tích màu xanh lá nhằm lưu dãy ô new cho Name đã làm được chọn.
Bạn cũng đều có thể biến đổi Name bằng phương pháp chọn nó vào danh sách, click Edit, chỉnh sửa tên trong vỏ hộp thoại Edit Name cùng nhấp vào OK. Trong vỏ hộp thoại này, bạn cũng có thể thay đổi phạm vi ô. Để xóa một tên, bạn chọn Name trong list và click vào Delete.
2. Tạo nên một danh sách xổ xuống phụ thuộc
Một danh sách thả xuống phụ thuộc vào là list mà trong số đó các tùy chọn đổi khác dựa trên sự lựa chọn tại 1 danh sách thả xuống khác.
Chẳng hạn như, khi bạn chọn Pizza trong danh sách thả xuống được tạo thành lúc đầu, một danh sách thả xuống phụ thuộc đựng nhiều loại pizza khác. Nếu bạn chọn Chinese, những tùy chọn trong danh sách thả xuống dựa vào thứ nhị chứa các loại pizza không giống nhau của Trung Quốc.
Trước khi thường xuyên bạn hãy quay trở về phần giải pháp tạo danh sách xổ xuống và chế tạo một list thả xuống Favorite Food chính, nếu như khách hàng chưa có.
Bây giờ, bạn sẽ tạo thêm ba danh sách nữa cùng đặt tên mang đến chúng. Nhập một danh sách vào trường đoản cú tùy chọn trong list thả xuống chính (Favorite Food). Lựa chọn một trong những list khác, nhập thương hiệu cho list đó trong Name Box cùng nhấn Enter. Lặp lại công việc đó cho từng danh sách.
Các tên của những danh sách không giống phải tương xứng với tùy chọn trong danh sách thả xuống chính. Ví dụ điển hình như, 1 trong ba danh sách khác của chúng ta chứa các loại bánh quy và được lấy tên là Cookies, như hiển thị mặt dưới. Nhị danh sách sót lại trong hộp màu đỏ được đặt tên là Pizza cùng Chinese.
Trước lúc tạo danh sách thả xuống phụ thuộc, các bạn phải lựa chọn một mục vào drop danh mục chính. Không quan trọng đặc biệt bạn chọn tùy lựa chọn nào. Sau đó, lựa chọn ô nơi bạn muốn thêm danh sách thả xuống phụ thuộc.
Click vào tab Data cùng nhấp vào Data Validation trong phần Data Tools. Lựa chọn List trong list dropdown Allow.
Nhập mẫu text tiếp sau đây vào vỏ hộp Source. Thay thế sửa chữa $B$2 bằng tham chiếu mang lại ô bao gồm chứa danh sách thả xuống chủ yếu của bạn. Giữ các dấu hiệu USD trong tham chiếu ô. Điều đó đã cho thấy một tham chiếu hoàn hảo nhất đến ô đó sẽ không đổi khác ngay cả lúc bạn xào luộc hoặc di chuyển công thức tham chiếu mang đến ô đó.
=INDIRECT($B$2)
Chức năng INDIRECT trả lại tham chiếu được định rõ bằng một chuỗi text. Trong trường thích hợp này, text trường đoản cú tùy lựa chọn đã được lựa chọn trong list thả xuống chính trong ô B2. Ví dụ điển hình như, nếu như bạn chọn Chinese từ danh sách thả xuống chính, =INDIRECT($B$2) trả lại tham chiếu Chinese. Do đó, danh sách thả xuống thiết bị hai chứa những mục Chinese. Bấm chuột vào OK.
Danh sách vào drop menu Favorite Disk bên dưới đây chuyển đổi phụ nằm trong vào tùy lựa chọn nào được lựa chọn trong list thả xuống Favorite Food.
3. Xào luộc và dán một danh sách xổ xuống
Nếu các bạn cần coppy một list thả xuống với tất cả dữ liệu qua 1 ô khác, thì hãy xào nấu và dán ô áp dụng Ctrl + C với Ctrl + V. Làm việc này xào nấu drop menu và định dạng của nó.
Nếu chúng ta chỉ muốn sao chép danh sách thả xuống với dữ liệu nhưng không bao gồm định dạng thì lựa chọn ô và xào nấu nó một cách bình thường sử dụng Ctrl + C. Sau đó, đi mang đến tab Home với click vào Paste vào phần Clipboard. ở đầu cuối chọn Paste Special.
Trong hộp thoại Paste Special, các bạn chọn Validation vào phần Paste và click vào OK. Điều này chỉ coppy drop các mục và không bao hàm định dạng vào ô ban đầu.
4. Chọn toàn bộ ô chứa danh sách thả xuống
Do nút mũi tên trỏ xuống không hiện trên drop list trừ lúc ô đó được chọn, thật cạnh tranh biết ô nào chứa list thả xuống. Bạn có thể áp dụng định dạng khác nhau cho ô cất drop list. Mặc dù nhiên, bạn cần tìm tất cả danh sách thả xuống trước, nếu không định đạng chúng. Có một cách chọn toàn thể drop danh sách trong Excel, được cho phép bạn biết địa chỉ của chúng.
Đầu tiên, lựa chọn ô cất drop list. Cho tới tab Home cùng click Find và Select nghỉ ngơi phần Editing. Sau đó, chọn Go khổng lồ Special.
Tới vỏ hộp thoại Go to Special, chọn Data Validation. Tùy chọn All dưới Data Validation, chọn toàn bộ ô bất kỳ quy tắc xác thực tài liệu được áp dụng. Same chỉ lựa chọn ô đựng drop list sử dụng cùng kiểu dáng quy tắc xác thực tài liệu với dữ liệu trong ô sẽ chọn. Click OK sau khoản thời gian hoàn tất những lựa chọn.
Giờ chúng ta có thể định dạng các ô này để khác nhau chúng với các ô khác cùng sẽ biết toàn bộ vị trí của drop list. Còn nếu như không muốn áp dụng định dạng khác đến drop list, hãy tham khảo hướng dẫn mặt dưới.
5. Làm cho mũi tên cù xuống luôn luôn hiển thị
Nút mũi tên xoay xuống trong list thả xuống không hiển thị lúc ô ko được chọn. Chúng ta sẽ giải quyết vấn đề này bằng phương pháp thêm một nút mũi tên con quay xuống vào phía bên phải list thả xuống.
Để bắt đầu bạn sở hữu ảnh mũi tên thả xuống (click chuột buộc phải vào liên kết đó và chọn Save link as). Sau đó, chọn ô ở mặt phải list thả xuống và đi mang đến tab Insert.
Bạn click vào Illustrations > Pictures.
Trong hộp thoại Insert Picture, các bạn điều đào bới nơi chúng ta lưu ảnh mũi tên và lựa chọn file đó. Tiếp theo, click vào Insert.
6. Xóa list thả xuống từ một ô
Nếu bạn có nhu cầu xóa một list thả xuống từ một ô, các bạn chọn ô đó và hộp thoại Data Validation như đã được đề cập cho (tab Data > Data Validation trong phần Data Tools). Click vào Clear All.
Các tùy chọn trong vỏ hộp thoại Data Validation được reset về khoác định của chúng. Bạn nhấp chuột vào OK.
Danh sách thả xuống được sa thải và ô được hồi phục về định dạng mặc định của nó. Nếu bao gồm một chọn lọc được chọn khi chúng ta gỡ bỏ list thả xuống, thì ô này được nhập với mức giá trị của tùy lựa chọn đó.
Nếu bạn không muốn ngẫu nhiên giá trị nào được gìn giữ khi xóa list thả xuống, chúng ta có thể sao chép ô trống cùng dán nó vào ô gồm chứa list thả xuống. Danh sách thả xuống được xóa với ô biến một ô trống. Tận dụng triệt để danh sách thả xuống đến mục nhập dữ liệu trong Excel làm khiến cho bạn tăng năng suất quá trình cũng như làm cho cho cuộc sống thường ngày dễ dàng hơn.
7. Tạo danh sách tùy trở nên trong Excel
Bạn có thể dùng tuấn kiệt Custom các mục để tạo nên và tàng trữ danh sách thường sử dụng. Chúng có thể được cần sử dụng làm tiêu đề cho cột và hàng hoặc điền dữ liệu vào danh sách thả xuống Excel nhanh, đúng mực hơn.
Tạo list tùy phát triển thành trên WindowsClick tab File.Chọn Options ở bên trái.Trong vỏ hộp thoại Excel Options, click Advanced.Cuộn xuống dưới phần General ở bên buộc phải và click Edit Custom Lists.Trong box Custom lists, bạn sẽ thấy những danh sách ngày và tháng được khẳng định trước vào năm.Tạo một list tùy biến chuyển trên Mac
Click Excel > Preferences từ thanh menu.Chọn Custom List.
Giống như Windows, bạn sẽ thấy những list được tích hòa hợp sẵn như những ngày viết tắt trong tuần và tháng của một năm.
Hướng dẫn này của Học Excel Online trình diễn bốn cách lập cập để sản xuất một list tùy chọn của Excel (hay nói một cách khác là tạo list trong Excel) – dựa trên một danh sách các giá trị, phạm vi của ô, dải ô chọn cái tên và một danh sách tùy lựa chọn động. Đồng thời cũng thấy phương pháp để tạo ra một list tùy chọn xuất phát điểm từ một bảng tính khác, sửa đổi và xóa danh sách.
Danh sách tùy chọn của Excel, hoặc danh sách tùy lựa chọn hoặc vỏ hộp kết hợp, được áp dụng để nhập dữ liệu vào bảng tính từ bỏ danh sách các mục được xác minh trước. Mục đích chính của bài toán sử dụng các danh sách tùy chọn trong Excel là để hạn chế số lượng những lựa chọn tất cả sẵn cho những người dùng. Ngoài ra, danh sách tùy chọn rào cản lỗi thiết yếu tả và khiến cho dữ liệu nhập cấp tốc hơn.
Xem nhanh
Làm thay nào để tạo ra một list tùy chọn Excel
Về tổng thể, bao gồm 4 phương pháp để thực hiện nay một list tùy chọn trong Excel, và tất cả chúng đều sở hữu những ưu thế và yếu hèn riêng. Dưới đó là sơ lược những ưu thế và điểm yếu chính cũng giống như hướng dẫn cụ thể từng cách cho từng phương pháp:Tạo danh sách tùy chọn có giá trị được phân bóc tách bằng dấu phẩy
Đây là cách sớm nhất để tạo danh sách tùy lựa chọn chỉ vào 3 cách trên tất cả các phiên bạn dạng Excel 2013, 2010, 2007 cùng 2003.
lựa chọn 1 ô hoặc hàng ô cho list tùy lựa chọn của bạnBạn bắt đầu bằng cách chọn một ô hoặc nhiều ô mà bạn muốn danh sách tùy chọn xuất hiện. Đây hoàn toàn có thể là một ô duy nhất, một dải ô hoặc cục bộ cột. Nếu chúng ta chọn toàn cục cột, một danh sách tùy lựa chọn sẽ được tạo ra trong mỗi ô của cột đó, kia gọi là một trình tiết kiệm ngân sách thời gian, ví dụ như như khi chúng ta cần chế tạo ra bảng câu hỏi.
Bạn thậm chí có thể chọn những ô ko tiếp giáp bằng cách nhấn cùng giữ phím Ctrl trong khi chọn ô bởi chuột.
Trên thanh cuộn của Excel, hãy đưa đến tab Data > Data Tools và nhấp vào Data Validation.
Trong cửa sổ Data Validation, trên tab Settings, hãy tiến hành theo công việc sau:
Trong hộp Allow, chọn List.Trong hộp Source, nhập những mục mà bạn muốn xuất hiện trong list tùy chọn của bạn phân bí quyết nhau bằng dấu phẩy (có hoặc không tồn tại dấu cách).Đảm bảo chọn ô In-cell dropdown.Theo tùy chọn, chọn Ignore blanks nếu bạn có nhu cầu cho phép người dùng làm trống ô.Nhấp OK và bạn đã hoàn tất!
Bây giờ, người dùng Excel chỉ cần nhấp vào mũi tên sát bên một ô có list tùy chọn, và tiếp nối chọn những mục có trong đó.
Xem thêm: Bí mật về 'bàn là con gà' được săn lùng có giá hàng trăm tỷ đồng là gì?
Nếu bạn muốn chỉnh sửa list tùy lựa chọn Excel của công ty trong tương lai, các bạn sẽ phải thay đổi tất cả các ô trích dẫn trường đoản cú Data Validation. Đây chưa phải là sự việc lớn nếu bạn chỉ bao gồm một vài ba ô vậy nên nằm trên và một sheet, nhưng nếu khách hàng có hàng chục hoặc hàng trăm ô rải rác rến trên những bảng tính khác nhau, vẫn tốn kha khá thời hạn để tra cứu và chỉnh sửa chúng.
Tạo danh sách tùy chọn của Excel dựa trên một phạm vi được đặt tên
Phương pháp tạo thành một list tùy chọn trong Excel tốn nhiều thời hạn hơn, tuy nhiên về lâu về lâu năm nó rất có thể tiết kiệm nhiều thời gian hơn.
Nhập những mục cho danh sách tùy chọn của bạn.Chọn các mục mà bạn có nhu cầu xuất hiện trong danh sách tùy chọn của người sử dụng trong một bảng tính hiện tất cả hoặc gõ các mục trong một trang tính mới. Các giá trị này yêu cầu được nhập vào một cột hoặc một hàng mà lại không có ngẫu nhiên ô trống nào.
Ví dụ: hãy tạo list tùy chọn của các thành phần cho cách làm nấu ăn yêu thích của bạn:
Bạn thực sự có thể bỏ qua đoạn này và tạo list tùy chọn dựa trên một dãy ô , nhưng những dải ô chọn cái tên thực sự hỗ trợ cho việc quản lý các danh sách tùy chọn của Excel tiện lợi hơn.
Chọn toàn bộ các mục mà bạn muốn đưa vào list tùy chọn, bấm vào phải vào chúng, với chọn Define Name, từ list tùy chọn. Ngoài ra, bạn có thể nhấp vào Name Manager trên tab Formulas hoặc nhấn Ctrl + F3.Trong hộp thoại Name Manager, nhấp vào New…Trong trường Name, để tên mang lại mục nhập của bạn, hãy chắc chắn rằng rằng vào hộp Refers to đã hiển thị đúng đắn vùng địa chỉ của các mục đã nhập, và kế tiếp nhấp vào Ok. Đảm bảo không có dấu cách hay vệt gạch ngang trong tên của vùng phạm vi chúng ta vừa đặt, hãy sừ dụng vết gạch bên dưới (_) nuốm thế.
Mẹo. Một bí quyết nhanh hơn để tạo nên một phạm vi được lấy tên trong Excel là chọn các ô cùng đặt tên dải ô một biện pháp trực tiếp ở Name Box. Khi trả tất, nhấp Enter để lưu phạm vi chọn cái tên mới tạo.
Danh sách tùy chọn dựa trên một bảng
Thay bởi vì một phạm vi chọn cái tên thông thường, chúng ta có thể cân nhắc chuyển đổi danh sách của chúng ta thành 1 bảng Excel với khá đầy đủ chức năng ( Insert > Table) trước, tiếp đến tạo tên cho list đó.
Để tiến hành việc này, bạn cũng có thể nhập =your_table_name
Đơn giản chỉ việc nhấp vào ô mà bạn có nhu cầu có danh sách tùy chọn. Điều này hoàn toàn có thể nằm trong và một bảng, nơi danh sách mục của người sử dụng được để hoặc vào một bảng tính khác. Bạn cũng có thể có thể lựa chọn một dải ô, hoặc toàn bộ cột.
Trên thanh cuộn băng của Excel, đến tab Data và nhấp vào Data Validation.
Trong cửa sổ Data Validation, hãy đưa tới tab Settings cùng chọn những tùy lựa chọn sau:
Trong hộp Allow, chọn List .Trong hộp Source, nhập vào tên của vùng mà chúng ta đã đặt tại bước trên, đằng trước đặt thêm dấy bằng, ví dụ =Ingredients.Đảm bảo lựa chọn ô In-cell dropdown.Đánh dấu vào Ignore blanks nếu người tiêu dùng được phép nhằm ô trống.Và cuối cùng, nhấp vào nút OK để hoàn tất việc tạo list tùy lựa chọn của bạn.
Nếu list nguồn chứa nhiều hơn 8 mục, danh sách tùy lựa chọn của các bạn sẽ có một thanh cuộn như sau:
Việc tạo nên một list tùy chọn dựa vào một hàng ô giống như như việc tạo ra một danh sách tùy chọn dựa trên phạm vi được đặt tên, chỉ tất cả hai sự không giống biệt:
Bạn quăng quật qua bước 2 – chế tác một phạm vi được để tên .Trong bước 5 , khi thiết đặt danh sách tùy lựa chọn của bạn, thay vì nhập tên của dải ô, hãy nhấp vào biểu tượng Collapse Dialog bên cạnh hộp Source và chọn tất cả các ô có mục nhập bạn có nhu cầu đưa vào list tùy lựa chọn của bạn. Chúng có thể ở trong thuộc hoặc trong một bảng tính khác. Nếu sinh hoạt bảng tính khác, bạn chỉ việc đi đến sheet kia và lựa chọn một phạm vi bằng phương pháp sử dụng chuột.
Khóa học tập Excel nâng cao Hà Nội
Tạo một list tùy lựa chọn động (tự hễ cập nhật)
Nếu bạn liên tục chỉnh sửa những mục trong list tùy chọn, bạn cũng có thể muốn tạo danh sách tùy chọn động trong Excel. Trong trường hòa hợp này, list của các bạn sẽ được cập nhật tự động trong tất cả các ô tất cả chứa nó, khi chúng ta xoá hoặc thêm các mục new vào list nguồn.
Cách đơn giản nhất để tạo list tùy chọn được cập nhật tự động hóa trong Excel là sản xuất một danh sách mang tên dựa trên một bảng . Nếu vì nguyên nhân nào đó bạn muốn một phạm vi tên thông thường, thì tìm hiểu thêm cách thực hiện công thức OFFSET, như được lý giải bên dưới.
Bạn bắt đầu bằng giải pháp tạo một danh sách tùy chọn thông thường dựa trên một phạm vi được đặt tên như biểu lộ ở trên.Trong bước 2, khi chế tạo ra một tên, các bạn đặt cách làm sau vào hộp Refers to.=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
Trong đó:
Sheet1 – thương hiệu của sheet đóA – cột chỗ chứa những mục của list tùy chọn của bạn$A$1 – ô đựng mục thứ nhất trong danh sách
Như các bạn thấy, công thức bao gồm 2 hàm Excel – OFFSET với COUNTA. Hàm COUNTA tính tất cả các ô gồm ký tự trong cột được chỉ định. OFFSET rước số đó và trả về một tham chiếu cho một phạm vi chỉ bao hàm các ô bao gồm ký tự, ban đầu từ ô trước tiên bạn chỉ định trong công thức.
Chi ngày tiết về OFFSET
Nếu bạn hiếu kỳ muốn gồm hiểu xúc tích của phương pháp trên.
Trong Microsoft Excel, hàm OFFSET trả về một tham chiếu đến một dãy bao gồm một số lượng các hàng và cột nhất định. Bạn có thể sử dụng nó trong số trường hợp không giống nhau khi bạn phải có một phạm vi động, có nghĩa là liên tục cụ đổi.
Cú pháp của hàm OFFSET như sau:
OFFSET(reference, rows, cols,
Vì vậy, trong phương pháp OFFSET của chúng ta =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1), họ xác định những đối số sau:
reference – ô $A$1 vào Sheet1, là mục thứ nhất trong danh sách tùy lựa chọn của bạn;rows & cols – 0 vì các bạn không muốn thay đổi phạm vi trả về theo theo hướng dọc hoặc chiều ngang;height – số ô ko rỗng vào cột A, được trả về vì hàm COUNTA;width – 1, tức là một cột.Tạo một danh sách tùy chọn xuất phát từ một bảng tính khác
Bạn hoàn toàn có thể tạo một danh sách tùy chọn trong Excel bằng phương pháp sử dụng một danh sách xuất phát điểm từ 1 bảng có tác dụng nguồn. Để thực hiện việc này, bạn sẽ phải tạo nên 2 phạm vi được đặt tên – một trong các bảng tính nguồn cùng cái còn lại trong bảng tính mà bạn muốn sử dụng danh sách Data Validation.
Chú ý. Đối với list tùy chọn xuất phát điểm từ một bảng tính khác để làm việc, bảng tính với danh sách nguồn buộc phải được mở.
Danh sách tùy lựa chọn tĩnh xuất phát từ 1 bảng tính khác
Danh sách tùy chọn lựa được tạo theo cách này sẽ không cập nhật tự động hóa khi các bạn thêm hoặc xóa những mục trong danh sách nguồn và các bạn sẽ phải sửa thay đổi phần trích dẫn danh sách nguồn theo cách thủ công.
tạo một phạm vi chọn cái tên cho danh sách nguồnMở bảng tính tất cả chứa list nguồn trong lấy một ví dụ này là Source
Book.xlsx và sinh sản một phạm vi được đặt tên cho những mục mà bạn có nhu cầu đưa vào list tùy chọn, ví dụ Source_list .
Mở bảng tính mà bạn muốn danh sách tùy chọn xuất hiện và tạo một thương hiệu trích dẫn đến danh sách nguồn của bạn. Trong ví dụ này, tham chiếu chấm dứt là = Source
Book.xlsx! Source_list
Trong bảng tính chính, chọn những ô cho list tùy chọn của bạn, nhấp vào Data > Data Validation và nhập tên chúng ta đã sinh sản trong cách 2 trong hộp Source.
Danh sách tùy tuyển chọn được tạo theo cách này vẫn được cập nhật ngay khi chúng ta thực hiện bất kỳ thay thay đổi nào đối với danh sách nguồn.
Tạo một thương hiệu phạm vi vào bảng tính mối cung cấp với công thức OFFSET, như được lý giải trong Tạo một list tùy lựa chọn động .Trong bảng tính chính, áp dụng Data Validation theo cách thông thường.Data Validation không hoạt động
Tùy lựa chọn Data Validation tất cả màu xám hoặc không khả dụng? Có một vài nguyên nhân hoàn toàn có thể xảy ra:
Danh sách tùy chọn cần yếu được chế tạo bảng tính được đảm bảo hoặc chia sẻ. Loại bỏ bảo đảm hoặc ngừng chia sẻ bảng tính, và kế tiếp thử nhấp lại vào Data Validation một lần nữa.Bạn đang tạo nên một list tùy chọn xuất phát từ 1 bảng Excel được liên kết đến một trang SharePoint. Hủy liên kết bảng hoặc xóa định dạng bảng cùng thử lại.
Các tùy chọn bổ sung cho list tùy lựa chọn của Excel
Trong hầu hết các ngôi trường hợp, các tùy chọn của tab Settings chúng ta đã bàn bạc ở trên trọn vẹn đầy đủ. Nếu không ta cũng có thể có thêm tùy chọn khác bao gồm sẵn trên những tab khác của cửa sổ vỏ hộp thoại Data Validation.
Hiển thị một thông tin khi nhấp vào trong 1 ô tất cả chứa list tùy chọn
Nếu bạn muốn hiển thị cho những người dùng một thông báo bật lên khi họ nhấp vào bất kỳ ô như thế nào chứa danh sách tùy lựa chọn của bạn, hãy thường xuyên theo bí quyết này:
Trong vỏ hộp thoại Xác thực dữ liệu (Data tab > Data Validation), đưa sang tab Input Message.Hãy bảo đảm chọn Show đầu vào message when cell is selectedNhập tiêu đề và thông báo vào các trường tương xứng (tối nhiều 225 ký kết tự).Nhấp vào nút OK để lưu tin nhắn và đóng hộp thoại.
Kết quả trong Excel sẽ y hệt như sau:
Tổng hợp thủ thuật Excel hay nhất đông đảo thời đại
Cho phép người dùng nhập dữ liệu của họ vào một hộp kết hợp
Theo khoác định, danh sách tùy lựa chọn mà bạn tạo ra trong Excel là không thể chỉnh sửa, có nghĩa là bị giới hạn trong những giá trị trong danh sách. Tuy nhiên, chúng ta có thể cho phép người tiêu dùng nhập các giá trị của mình vào hộp.
Về khía cạnh kỹ thuật, vấn đề đó sẽ trở thành một list tùy chọn vào trong 1 hộp phối kết hợp Excel. Thuật ngữ “hộp kết hợp” tức là một danh sách tùy chọn hoàn toàn có thể chỉnh sửa chất nhận được người dùng chọn một giá trị từ danh sách hoặc nhập một cực hiếm trực tiếp vào hộp.
Trong hộp thoại Data Validation (Data tab > Data Validation), mang đến tab Error Alert Chọn hộp kiểm “Show error alert after invalid data is entered” nếu bạn muốn hiển thị lưu ý khi fan dùng nỗ lực nhập một số dữ liệu vào hộp kết hợp khác cùng với trong list tùy chọn của người tiêu dùng (điều này sẽ không ngăn tín đồ nhập dữ liệu riêng của họ). Nếu bạn không thích một thông tin xuất hiện, hãy vứt chọn nó.Nếu chúng ta đã lựa chọn hiển thị một thông điệp cảnh báo, hãy chọn một trong các tuỳ lựa chọn từ hộp Stylevà nhập tiêu đề với thông báo. Chọn Hoặc Information hoặc Warning để cho phép người áp dụng nhập văn bạn dạng riêng của mình trong vỏ hộp combo. Các tùy lựa chọn chỉ không giống nhau về biểu tượng được hiển thị thuộc với thông tin và nút được lựa chọn theo mặc định.Một lời nhắn Information sẽ được hiển thị với “i” hình tượng và nút chọn OK. Nên chọn tùy lựa chọn này được nếu bạn dùng của chúng ta phải nhập những lựa lựa chọn của riêng chúng ta khá hay xuyên.Một lời nhắn Warningsẽ được hiển thị cùng với “!” Biểu tượng cùng nút “No” được chọn trước. Đây được xem như là một cách giỏi để hiển thị lưu ý nếu bạn muốn người sử dụng của mình lựa chọn 1 mục từ list tùy chọn thay vày nhập tài liệu của riêng rẽ họ.Stop sẽ ngăn fan khác nhập ngẫu nhiên dữ liệu nào không có trong list tùy chọn Excel của bạn.Và đấy là cách thông tin cảnh báo thiết lập của bạn mở ra trong Excel:
Mẹo. Nếu bạn lần khần nhập tiêu đề hoặc lời nhắn nào, chúng ta cũng có thể để trống. Trong trường đúng theo này, Microsoft Excel sẽ hiển thị cảnh báo mặc định “The value you entered is not valid. A user has restricted values that can be entered into this cell.”
Làm thay nào để sửa đổi một danh sách tùy lựa chọn Excel
Sau khi bạn tạo một list tùy chọn trong Excel, bạn cũng có thể muốn thêm các mục vào nó hoặc xóa một trong những mục hiện nay có. Làm nỗ lực nào chúng ta làm điều này phụ thuộc vào danh sách tùy chọn của người tiêu dùng đã được tạo thành ra.
Chỉnh sửa danh sách tùy chọn bằng dấu phẩy
Nếu chúng ta đã chế tác một danh sách tùy chọn bằng dấu phẩy, hãy tiếp tục quá trình sau:
Chọn một ô hoặc các ô trích dẫn list Data Validation Excel của bạn, ví dụ các ô tất cả chứa list tùy lựa chọn mà bạn muốn chỉnh sửa.Nhấp vào Data Validation (Excel ribbon > Data tab).Xóa hoặc nhập các mục bắt đầu trong hộp Source.Nhấn OK nhằm lưu các chuyển đổi và đóng cửa sổ Data Validation Excel .
Chỉnh sửa danh sách tùy chọn dựa trên một dải ô
Nếu bạn đã tạo thành một danh sách tùy chọn bằng phương pháp chỉ định một hàng ô chứ không phải là tham chiếu mang đến một dải ô được để tên, thì tiến hành theo cách sau.
Đi qua bảng tính bao gồm chứa các mục xuất hiện thêm trong danh sách tùy chọn của doanh nghiệp và sửa đổi danh sách theo phong cách bạn muốn.Chọn ô hoặc ô có chứa danh sách tùy chọn của bạn.Nhấp Data Validation trên tab Data.Trong cửa sổ Data Validation Excel , trên tab Settings, thay đổi các tham chiếu ô trong hộp Nguồn. Bạn rất có thể chỉnh sửa chúng bằng tay hoặc nhấp vào biểu tượng Collapse Dialog.Nhấp vào nút OKđể lưu các chuyển đổi và tạm dừng hoạt động sổ.
Mẹo. Nếu bạn có nhu cầu áp dụng các biến hóa cho tất cả những ô có chứa list tùy chọn này, nên chọn tùy lựa chọn “Apply these changes to all other cells with the same settings
Chỉnh sửa list tùy chọn của Excel dựa vào một phạm vi được đặt tên
Nếu bạn đã tạo ra một danh sách tùy chọn lựa được đặt tên, thì các bạn chỉ hoàn toàn có thể chỉnh sửa các mục của phạm vi của bản thân mình và sau đó biến đổi trích dẫn mang lại phạm vi được Đặt tên. Tất cả các danh sách tùy chọn dựa trên phạm vi được đặt tên này đã được cập nhật tự động.
Thêm hoặc xóa những mục vào phạm vi được đặt tên. Mở bảng tính chứa phạm vi được đặt tên của bạn, xóa hoặc nhập mới. Hãy nhớ sắp đến xếp những mục theo trang bị tự bạn có nhu cầu chúng xuất hiện thêm trong list tùy chọn Excel của bạn.Thay thay đổi tham chiếu mang đến phạm vi được Đặt tên.Trên ribbon Excel, đi đến tab Formulas > Name Manager. Ngoài ra, nhấn Ctrl + F3để mở cửa sổ Formulas tab > Name Manager.Trong cửa sổ Name Manager, nên chọn phạm vi đã đặt tên mà bạn có nhu cầu cập nhật.Thay đổi tham chiếu trong hộp Refers to bằng phương pháp nhấp vào biểu tượng Collapse Dialog với chọn tất cả các mục nhập cho danh sách tùy lựa chọn của bạn.Nhấp vào nút Close, sau đó trong thông báo xác nhận xuất hiện, nhấp vào Yesđể giữ các chuyển đổi của bạn.
Làm cố gắng nào để xóa một list tùy chọn
Nếu bạn không muốn có danh sách tùy chọn trong bảng tính Excel, chúng ta có thể xóa bọn chúng khỏi một hoặc tất cả các ô.
Loại vứt một danh sách tùy lựa chọn từ ô đang chọn
Chọn một ô hoặc vài ba ô cơ mà từ đó bạn muốn bỏ các danh sách tùy chọn.Chuyển đến tab Data và nhấp Data Validation.Trên tab cài đặt đặt, lựa chọn nút Clear All.
Nếu bạn muốn xóa cả list tùy chọn và giá trị của các ô, chúng ta cũng có thể chọn những ô cùng nhấp vào nút Clear all trên tab Home > Editing > Clear
Xóa một list tùy lựa chọn của Excel từ toàn bộ các ô trong sheet hiện tại
Bằng bí quyết này, chúng ta có thể loại quăng quật một list tùy chọn từ toàn bộ các ô liên kết trong bảng tính hiện tại tại. Thao tác này sẽ không xóa cùng một list tùy lựa chọn từ các ô trong những bảng tính khác, ví như có.
Chọn bất kỳ ô chứa list tùy lựa chọn của bạn.Nhấp Data Validation trong tab Data.Trong cửa sổ Data Validation, trên tab Settings, lựa chọn hộp kiểm “Apply these changes to all other cells with the same settings“. Khi đang chọn, tất cả các ô trích xuất list Data Validation Excel này sẽ được chọn.Nhấp vào nút Clear Allđể xóa list tùy chọn .Nhấp OKđể lưu các biến đổi và đóng cửa sổ Data Validation.
Ngoài ra để nâng cao hơn kiến thức về Excel, các chúng ta có thể tham gia khóa học EX101 – Excel từ bỏ cơ bạn dạng tới nâng cấp dành cho những người đi làm cho của khối hệ thống Học Excel Online. Trong khóa huấn luyện và đào tạo này các các bạn sẽ được tò mò những kỹ năng đầy đủ, có hệ thống về các hàm, các công cố kỉnh của Excel, vấn đề ứng dụng những hàm, nguyên lý đó vào vào công việc. Hiện thời hệ thống có nhiều ưu đãi cho mình khi đăng ký khóa đào tạo này
