Cách thêm lựa chọn trong excel
Hướng dẫn này của Học Excel Online trình diễn bốn cách gấp rút để tạo ra một danh sách tùy chọn của Excel (hay còn được gọi là tạo các mục trong Excel) – dựa vào một danh sách các giá trị, phạm vi của ô, dải ô được đặt tên và một list tùy lựa chọn động. Đồng thời cũng thấy phương pháp để tạo ra một danh sách tùy chọn xuất phát từ một bảng tính khác, sửa đổi và xóa danh sách. Bạn đang xem: Cách thêm lựa chọn trong excel
Danh sách tùy lựa chọn của Excel, hoặc list tùy chọn hoặc hộp kết hợp, được thực hiện để nhập dữ liệu vào bảng tính trường đoản cú danh sách các mục được khẳng định trước. Mục đích thiết yếu của việc 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 bao gồm sẵn cho những người dùng. Ngoài ra, list tùy chọn rào cản lỗi chính tả và khiến cho dữ liệu nhập cấp tốc hơn.
Xem nhanh
Làm cố kỉnh nào để tạo ra một list tùy chọn Excel
Về tổng thể, có 4 phương pháp để thực hiện một list tùy lựa chọn trong Excel, và tất cả chúng đều có những ưu điểm và yếu riêng. Dưới đấy là sơ lược những điểm mạnh và yếu điểm chính cũng như hướng dẫn chi tiết 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 tách bóc bằng dấu phẩy
Đây là cách sớm nhất có thể để tạo list tùy lựa chọn chỉ trong 3 bước trên tất cả các phiên bản Excel 2013, 2010, 2007 và 2003.
lựa chọn một ô hoặc dãy ô 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 các ô 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 toàn cục cột. Nếu các bạn chọn cục bộ cột, một danh sách tùy lựa chọn sẽ được tạo nên trong mỗi ô của cột đó, đó gọi là 1 trong những trình tiết kiệm thời gian, ví dụ như khi chúng ta cần chế tạo bảng câu hỏi.
Bạn thậm chí có thể chọn những ô ko tiếp giáp bằng phương pháp 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 chuyển đến tab Data > Data Tools và nhấp vào Data Validation.
Trong cửa sổ Data Validation, trên tab Settings, hãy triển khai 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 có nhu cầu xuất hiện trong danh sách tùy chọn của doanh nghiệp phân bí quyết nhau bởi dấu phẩy (có hoặc không có 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à các bạn đã hoàn tất!
Bây giờ, người tiêu dùng Excel chỉ cần nhấp vào mũi tên ở bên cạnh một ô có list tùy chọn, và sau đó chọn các mục tất cả trong đó.
Nếu bạn có nhu cầu chỉnh sửa list tùy chọn Excel của khách hàng trong tương lai, bạn sẽ phải thay đổi tất cả những ô trích dẫn tự Data Validation. Đây không hẳn là sự việc lớn nếu như khách hàng chỉ có một vài ô bởi thế nằm trên cùng một sheet, nhưng nếu như khách hàng có hàng trăm hoặc hàng nghìn ô rải rác rưởi trên những bảng tính khác nhau, vẫn tốn kha khá thời hạn để search và chỉnh sửa chúng.
Tạo danh sách tùy lựa chọn của Excel dựa vào một phạm vi được để tên
Phương pháp chế tạo ra một danh sách tùy chọn trong Excel tốn nhiều thời gian hơn, tuy vậy về lâu về lâu năm nó 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 những mục mà bạn muốn xuất hiện tại trong list tùy chọn của người tiêu dùng trong một bảng tính hiện bao gồm hoặc gõ những mục vào một trang tính mới. Các quý giá này phải được nhập vào một cột hoặc một hàng nhưng không có ngẫu nhiên ô trống nào.
Ví dụ: hãy tạo danh sách tùy chọn của các thành phần cho phương pháp nấu ăn hâm mộ của bạn:
Bạn thực sự hoàn toàn có thể bỏ qua bước này và tạo danh sách tùy chọn dựa bên trên một hàng ô , nhưng các dải ô được đặt tên thực sự giúp cho việc thống trị các list tùy chọn của Excel dễ ợt hơn.
Chọn tất cả các mục mà bạn có nhu cầu đưa vào list tùy chọn, nhấp chuột phải vào chúng, cùng 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 vỏ hộp thoại Name Manager, nhấp vào New…Trong trường Name, để tên đến mục nhập của bạn, hãy chắc hẳn rằng rằng vào hộp Refers to đã hiển thị đúng mực vùng địa chỉ của các mục đã nhập, và tiếp nối nhấp vào Ok. Đảm bảo không có dấu biện pháp hay vệt gạch ngang trong thương hiệu của vùng phạm vi các bạn vừa đặt, hãy sừ dụng vệt gạch bên dưới (_) cầm thế.
Mẹo. Một bí quyết nhanh hơn để tạo thành một phạm vi chọn cái tên trong Excel là chọn các ô cùng đặt tên dải ô một cách trực tiếp ở Name Box. Khi hoàn tất, nhấp Enter nhằm lưu phạm vi được lấy tên mới tạo.
Danh sách tùy chọn dựa trên một bảng
Thay vị một phạm vi chọn cái tên thông thường, bạn cũng có thể cân nhắc chuyển đổi danh sách của người tiêu dùng thành 1 bảng Excel với rất đầy đủ chức năng ( Insert > Table) trước, tiếp nối tạo thương hiệu cho list đó.
Để triển khai việc này, chúng ta có thể nhập =your_table_name
Đơn giản chỉ cần nhấp vào ô mà bạn muốn có list tùy chọn. Điều này rất 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 đều có thể chọn 1 dải ô, hoặc cục 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 và chọn những tùy chọn sau:
Trong hộp Allow, chọn List .Trong hộp Source, nhập vào thương hiệu của vùng mà các bạn đã đặt ở 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 dùng được phép để ô trống.Và cuối cùng, nhấp vào nút OK để hoàn tất câu hỏi 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, list tùy lựa chọn của bạn sẽ có một thanh cuộn như sau:
Việc tạo ra một danh sách tùy chọn dựa trên một dãy ô giống như như việc tạo nên một danh sách tùy chọn dựa trên phạm vi được để tên, chỉ tất cả hai sự không giống biệt:
Bạn bỏ qua bước 2 – sản xuất một phạm vi được đặt tên .Trong bước 5 , khi setup danh sách tùy 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 toàn bộ các ô có mục nhập bạn muốn đưa vào danh sách tùy chọn của bạn. Chúng có thể ở trong cùng hoặc vào một bảng tính khác. Nếu ngơi nghỉ bảng tính khác, bạn chỉ cần đi mang đến sheet đó và chọn 1 phạm vi bằng phương pháp sử dụng chuột.
Khóa học Excel cải thiện Hà Nội
Tạo một danh sách tùy lựa chọn động (tự cồn cập nhật)
Nếu bạn tiếp tục chỉnh sửa những mục trong list tùy chọn, chúng ta có thể muốn tạo list tùy chọn động trong Excel. Trong trường hòa hợp này, danh sách của các bạn sẽ được cập nhật auto trong tất cả các ô tất cả chứa nó, khi bạn xoá hoặc thêm những mục mới vào list nguồn.
Cách đơn giản nhất để tạo list tùy lựa chọn được cập nhật auto trong Excel là tạo ra một danh sách có tên dựa trên một bảng . Nếu vì lý do nào đó mình thích một phạm vi thương hiệu thông thường, thì xem thêm cách sử dụng công thức OFFSET, như được giải thích bên dưới.
Bạn bắt đầu bằng cách 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 thị ở trên.Trong cách 2, khi tạo một tên, bạn đặt bí quyết 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 nơi chứa các mục của list tùy chọn của bạn$A$1 – ô đựng mục đầu tiên trong danh sáchNhư các bạn thấy, công thức bao hàm 2 hàm Excel – OFFSET cùng COUNTA. Hàm COUNTA tính toàn bộ các ô có ký tự trong cột được chỉ định. OFFSET đem số đó và trả về một tham chiếu đến một phạm vi chỉ bao gồm các ô bao gồm ký tự, ban đầu từ ô thứ nhất bạn chỉ định trong công thức.
Chi máu về OFFSET
Nếu bạn tò mò và hiếu kỳ muốn có hiểu logic của công thức trên.
Trong Microsoft Excel, hàm OFFSET trả về một tham chiếu đến một dãy gồm 1 số lượng các hàng và cột nhất định. Bạn hoàn toàn có thể sử dụng nó trong số trường hợp không giống nhau khi bạn cần phải có một phạm vi động, tức là liên tục nắm đổi.
Cú pháp của hàm OFFSET như sau:
OFFSET(reference, rows, cols,
Xem thêm: Cách Làm Cây Cành Vàng Lá Ngọc Đẹp, Chậu Cây Cành Vàng Lá Ngọc
height, Tùy chọn – số sản phẩm trong phạm vi trả về.width, Tùy chọn – số cột trong trong phạm vi trả về.Vì vậy, trong công thức OFFSET của chúng ta =OFFSET(Sheet1!$A$1, 0, 0, COUNTA(Sheet1!$A:$A), 1), chúng ta xác định các đối số sau:
reference – ô $A$1 vào Sheet1, là mục trước tiên trong danh sách tùy lựa chọn của bạn;rows & cols – 0 vì chúng ta không muốn biến đổi phạm vi trả về theo chiều dọc củ hoặc chiều ngang;height – số ô ko rỗng vào cột A, được trả về bởi hàm COUNTA;width – 1, tức là một cột.Tạo một list tùy chọn từ một bảng tính khác
Bạn có thể tạo một list tùy lựa chọn trong Excel bằng cách 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. Để triển khai việc này, các bạn sẽ phải tạo thành 2 phạm vi chọn cái tên – 1 trong các bảng tính nguồn và 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ừ 1 bảng tính khác để gia công việc, bảng tính với list nguồn buộc phải được mở.
Danh sách tùy lựa chọn tĩnh từ một bảng tính khác
Danh sách tùy chọn lựa được tạo theo phong cách này sẽ không còn cập nhật auto khi chúng ta thêm hoặc xóa các mục trong list 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.
chế tạo ra một phạm vi chọn cái tên cho danh sách nguồnMở bảng tính có chứa list nguồn trong lấy ví dụ này là SourceBook.xlsx và sinh sản một phạm vi được lấy 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 mở ra và tạo ra 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 ngừng là = SourceBook.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 đã sản xuất trong cách 2 trong hộp Source.
Danh sách tùy lựa chọn được tạo theo cách này đã được cập nhật ngay khi bạn thực hiện bất kỳ thay thay đổi nào so với danh sách nguồn.
Tạo một thương hiệu phạm vi trong bảng tính mối cung cấp với bí quyết OFFSET, như được lý giải trong Tạo một danh sách tùy lựa chọn động .Trong bảng tính chính, vận dụng Data Validation theo phong cách thông thường.Data Validation ko hoạt động
Tùy lựa chọn Data Validation bao gồm màu xám hoặc không khả dụng? Có một vài nguyên nhân rất có thể xảy ra:
Danh sách tùy chọn bắt buộc được cung cấp bảng tính được bảo vệ hoặc phân chia sẻ. Loại bỏ đảm bảo an toàn hoặc hoàn thành chia sẻ bảng tính, và tiếp nối thử nhấp lại vào Data Validation một lần nữa.Bạn đang tạo nên một danh sách tùy chọn xuất phát điểm từ một bảng Excel được links đến một trang SharePoint. Hủy liên kết bảng hoặc xóa định hình bảng cùng thử lại.Các tùy chọn bổ sung cập nhật cho list tùy chọn của Excel
Trong phần nhiều các ngôi trường hợp, các tùy chọn của tab Settings chúng ta đã trao đổi ở trên trọn vẹn đầy đủ. Nếu ko ta cũng có thêm tùy lựa chọn khác có sẵn trên các tab không giống của cửa sổ vỏ hộp thoại Data Validation.
Hiển thị một thông báo khi nhấp vào một trong những ô có chứa list tùy chọn
Nếu bạn có nhu cầu hiển thị cho người dùng một thông báo bật lên khi họ nhấp vào ngẫu nhiên ô làm sao chứa list tùy lựa chọn của bạn, hãy thường xuyên theo biện pháp này:
Trong vỏ hộp thoại Xác thực dữ liệu (Data tab > Data Validation), gửi sang tab Input Message.Hãy bảo vệ chọn Show input đầu vào message when cell is selectedNhập title và thông tin vào các trường tương ứng (tối đa 225 ký tự).Nhấp vào nút OK để lưu giữ tin nhắn cùng đóng vỏ hộp thoại.
Kết trái trong Excel sẽ y hệt như sau:
Tổng hợp thủ thuật Excel xuất xắc nhất số đông thời đại
Cho phép người dùng nhập tài liệu của họ vào một hộp kết hợp
Theo mang định, list tùy chọn mà bạn tạo thành trong Excel là bắt buộc chỉnh sửa, tức là bị giới hạn trong số giá trị vào 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ề mặt kỹ thuật, vấn đề đó sẽ thay đổi một danh sách tùy chọn vào một hộp phối hợp Excel. Thuật ngữ “hộp kết hợp” có nghĩa là một list tùy chọ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 giá trị trực tiếp vào hộp.
Trong hộp thoại Data Validation (Data tab > Data Validation), cho tab Error Alert Chọn vỏ hộp kiểm “Show error alert after invalid data is entered” nếu bạn muốn hiển thị cảnh báo khi tín đồ dùng nỗ lực nhập một vài dữ liệu vào hộp phối kết hợp khác với trong danh sách tùy chọn của khách hàng (điều này sẽ không ngăn người nhập tài liệu riêng của họ). Nếu bạn không muốn một thông báo 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 lựa 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 để được cho phép người thực hiện nhập văn bạn dạng riêng của mình trong hộp combo. Các tùy chọn chỉ khác biệt về biểu tượng được hiển thị cùng với thông tin và nút được chọn theo khoác định.Một lời nhắn Information sẽ được hiển thị cùng với “i” biểu tượng và nút chọn OK. Nên chọn tùy chọn này được nếu fan dùng của khách hàng phải nhập các lựa lựa chọn của riêng chúng ta khá thường xuyên xuyên.Một lời nhắn Warningsẽ được hiển thị với “!” Biểu tượng và nút “No” được lựa chọn trước. Đây được coi là một cách tốt để hiển thị lưu ý nếu bạn có nhu cầu người cần sử dụng của mình lựa chọn một mục từ list tùy lựa chọn thay do nhập tài liệu của riêng họ.Stop sẽ ngăn bạn khác nhập ngẫu nhiên dữ liệu nào không có trong danh sách tùy chọn Excel của bạn.Và đấy là cách thông báo cảnh báo thiết lập của bạn lộ diện trong Excel:
Mẹo. Nếu bạn lần khần nhập tiêu đề hoặc tin nhắn nào, chúng ta có thể để trống. Trong trường đúng theo này, Microsoft Excel đã hiển thị lưu ý mặc định “The value you entered is not valid. A user has restricted values that can be entered into this cell.”
Làm vậy nào để sửa đổi một danh sách tùy chọn Excel
Sau khi bạn tạo một danh sách tùy lựa chọn trong Excel, bạn cũng có thể muốn thêm những mục vào nó hoặc xóa một vài mục hiện tại có. Làm vắt nào chúng ta làm điều này phụ thuộc vào vào list tùy chọn của người tiêu dùng đã được chế tạo ra ra.
Chỉnh sửa list tùy chọn bởi dấu phẩy
Nếu chúng ta đã chế tạo ra một danh sách tùy chọn bằng dấu phẩy, hãy tiếp tục công việc sau:
Chọn một ô hoặc các ô trích dẫn danh sách Data Validation Excel của bạn, ví dụ những ô có chứa danh sách tùy chọn mà bạn có nhu cầu chỉnh sửa.Nhấp vào Data Validation (Excel ribbon > Data tab).Xóa hoặc nhập những mục mới trong hộp Source.Nhấn OK nhằm lưu các đổi khác và đóng cửa sổ Data Validation Excel .
Chỉnh sửa danh sách tùy chọn dựa vào một dải ô
Nếu các bạn đã tạo ra một danh sách tùy chọn bằng phương pháp chỉ định một dãy ô chứ chưa phải là tham chiếu đến một dải ô được đặt tên, thì triển khai theo phương pháp sau.
Đi qua bảng tính có chứa các mục mở ra trong danh sách tùy chọn của người sử dụng và chỉnh sửa 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 , bên trên tab Settings, biến hóa các tham chiếu ô trong vỏ hộp Nguồn. Bạn có thể chỉnh sửa chúng thủ công bằng tay hoặc nhấp vào biểu tượng Collapse Dialog.Nhấp vào nút OKđể giữ các thay đổi và tạm dừng hoạt động sổ.
Mẹo. Nếu bạn muốn áp dụng các chuyển đổi cho tất cả các ô có chứa list tùy chọn này, hãy chọn tùy chọn “Apply these changes khổng lồ all other cells with the same settings
Chỉnh sửa list tùy lựa chọn của Excel dựa vào một phạm vi được để tên
Nếu các bạn đã chế tác một danh sách tùy tuyển chọn được đặt tên, thì bạn chỉ rất có thể chỉnh sửa những mục của phạm vi của bản thân và sau đó thay đổi trích dẫn cho phạm vi được Đặt tên. Tất cả những danh sách tùy chọn dựa vào phạm vi được lấy tên này vẫn được update tự động.
Thêm hoặc xóa những mục trong phạm vi được đặt tên. Mở bảng tính cất phạm vi chọn cái tên của bạn, xóa hoặc nhập mới. Hãy nhớ chuẩn bị xếp những mục theo đồ vật tự bạn muốn chúng xuất hiện thêm trong danh sách tùy chọn Excel của bạn.Thay thay đổi tham chiếu mang lại 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 lựa phạm vi sẽ đặt tên mà bạn muốn cập nhật.Thay thay đổi tham chiếu trong hộp Refers to bằng bí quyết nhấp vào biểu tượng Collapse Dialog cùng 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, tiếp đến trong thông báo chứng thực xuất hiện, nhấp vào Yesđể lưu các biến đổi của bạn.
Làm cố kỉnh nào để xóa một danh sách tùy chọn
Nếu bạn không thích có danh sách tùy lựa chọn trong bảng tính Excel, chúng ta có thể xóa chúng khỏi một hoặc toàn bộ các ô.
Loại vứt một danh sách tùy lựa chọn từ ô đã chọn
Chọn một ô hoặc vài ba ô mà lại từ đó bạn có nhu cầu bỏ những 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 có nhu cầu xóa cả list tùy chọn và giá trị của các ô, bạn cũng có thể chọn những ô với nhấp vào nút Clear all trên tab Home > Editing > Clear
Xóa một list tùy chọn của Excel từ tất cả các ô vào sheet hiện nay tại
Bằng giải pháp này, chúng ta cũng có thể loại bỏ một danh sách tùy lựa chọn từ toàn bộ các ô liên kết trong bảng tính hiện tại. Thao tác này sẽ không còn xóa cùng một danh sách tùy chọn từ các ô trong những bảng tính khác, trường hợp có.
Chọn ngẫu nhiên ô chứa danh sách tùy chọn của bạn.Nhấp Data Validation trong tab Data.Trong hành lang cửa số Data Validation, bên trên tab Settings, chọn hộp kiểm “Apply these changes to lớn all other cells with the same settings“. Khi sẽ chọn, tất cả các ô trích xuất danh sách Data Validation Excel này sẽ tiến hành chọn.Nhấp vào nút Clear Allđể xóa danh sách tùy chọn .Nhấp OKđể giữ các biến hóa và đóng cửa sổ Data Validation.
Ngoài ra để cải thiện hơn kiến thức về Excel, các chúng ta có thể tham gia khóa học EX101 – Excel từ cơ bản tới cải thiện dành cho những người đi làm của 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 kiến thức và kỹ năng đầy đủ, có khối hệ thống về các hàm, các công cố của Excel, việc ứng dụng các hàm, dụng cụ đó vào vào công việc. Hiện nay hệ thống có nhiều ưu đãi cho chính mình khi đăng ký khóa đào tạo và huấn luyện này
