Lựa chọn không hợp lệ Excel
Trang này có các mẹo xác thực dữ liệu và cho biết cách khắc phục các sự cố xác thực dữ liệu Excel, chẳng hạn như trình đơn thả xuống không hoạt động, chọn trống và không hiển thị tất cả các mục. Các video và các bước được viết bên dưới sẽ giúp bạn khắc phục sự cố danh sách thả xuống xác thực dữ liệu trong Microsoft Excel Show
GHI CHÚ. Nếu bạn cần trợ giúp về việc thiết lập danh sách thả xuống, hãy truy cập trang Danh sách thả xuống của Excel Tác giả. Debra Dalgleish Danh sách thả xuống mở ra với khoảng trống được chọnKhi bạn bấm vào mũi tên để mở danh sách thả xuống trên trang nhập dữ liệu, lựa chọn có thể chuyển sang khoảng trống ở cuối danh sách, thay vì mục đầu tiên trong danh sách. Tại sao điều này xảy ra, và làm thế nào bạn có thể ngăn chặn nó? Lấy file mẫu ở phần download Gây ra. Các ô trống trong danh sách nguồnTrong ví dụ được hiển thị ở trên, danh sách thả xuống dựa trên một phạm vi có tên là Sản phẩm. Người thiết lập danh sách để lại một số ô trống ở cuối, nơi có thể thêm các mục mới. Khi có một ô trống trong danh sách nguồn và ô có danh sách xác thực dữ liệu trống, danh sách sẽ mở ra với mục nhập trống được chọn GHI CHÚ. Một vấn đề khác có thể xảy ra nếu có khoảng trống trong danh sách nguồn --các mục nhập không hợp lệ có thể được cho phép trong các ô Sửa chữa. Sử dụng Danh sách nguồn độngThay vì để trống các ô trong danh sách nguồn, hãy sử dụng danh sách nguồn động sẽ tự động điều chỉnh khi bạn thêm hoặc xóa các mục
1. Bảng Excel được đặt tênĐể xem các bước tạo Bảng Excel, bạn có thể xem video ngắn này. Có các bước được viết trên trang Tạo bảng Excel 2. Phạm vi được đặt tên động với công thứcĐể xem các bước thiết lập dải ô được đặt tên động, bạn có thể xem video hướng dẫn ngắn này. Công thức OFFSET được hiển thị bên dưới video Công thức OFFSET được sử dụng trong ví dụ này là
Các mục bị thiếu trong danh sách thả xuốngNếu bạn thêm các mục mới ở cuối danh sách nguồn, các mục đó có thể bị thiếu khi bạn mở danh sách thả xuống sau này Đây là nguyên nhân phổ biến nhất của vấn đề đó và cách khắc phục. Xem cách bạn cũng có thể ngăn chặn vấn đề phổ biến này Gây ra. Thả xuống dựa trên danh sách tĩnhMột số trình đơn thả xuống dựa trên danh sách tĩnh, sử dụng một phạm vi cụ thể, chẳng hạn như Nếu một mục mới được nhập vào ô B5, mục đó sẽ không xuất hiện trong trình đơn thả xuống Sửa chữa. Thay đổi nguồn xác thực dữ liệuĐể khắc phục sự cố thiếu mục, hãy làm theo các bước sau
*Tên trong Hộp nguồn Thay vì địa chỉ, bạn có thể thấy tên trong hộp Nguồn, chẳng hạn như Để khắc phục điều đó
Ngăn chặn. Sử dụng danh sách nguồn độngĐể tránh sự cố thiếu mục với danh sách tĩnh, thay vào đó hãy sử dụng danh sách động. Có 2 cách để thiết lập chúng
Xem các video ở trên, hiển thị các bước cho cả hai tùy chọn Thiếu mũi tênĐôi khi, các mũi tên thả xuống xác thực dữ liệu không hiển thị trên trang tính, trong các ô mà bạn biết rằng danh sách xác thực dữ liệu đã được tạo Video này cho thấy những lý do phổ biến nhất khiến mũi tên bị thiếu. Hướng dẫn bằng văn bản để khắc phục sự cố bên dưới video Dưới đây là một vài nguyên nhân dẫn đến thiếu mũi tên để xác thực dữ liệu. Nhấp vào một liên kết để xem chi tiết Chỉ ô hoạt độngChỉ ô hiện hoạt trên trang tính mới hiển thị mũi tên thả xuống xác thực dữ liệu, ngay cả khi có nhiều ô có quy tắc xác thực dữ liệu. Để đánh dấu các ô chứa danh sách xác thực dữ liệu, bạn có thể tô màu các ô hoặc thêm nhận xét Nếu bạn yêu cầu mũi tên hiển thị cho tất cả các ô chứa danh sách, bạn có thể sử dụng hộp tổ hợp thay vì xác thực dữ liệu và các mũi tên đó sẽ luôn hiển thị. Để tạo hộp tổ hợp
Các đối tượng ẩnNếu các đối tượng bị ẩn trên trang tính, các mũi tên thả xuống xác thực dữ liệu cũng sẽ bị ẩn Để hiển thị các đối tượng, hãy sử dụng phím tắt -- Ctrl + 6 Hoặc làm theo các bước sau để thay đổi cài đặt Tùy chọn
Tùy chọn thả xuốngTrong hộp thoại Xác thực dữ liệu, bạn có thể tắt tùy chọn cho danh sách thả xuống. Để bật lại
Excel 2013 Windows 8Trong trường hợp bạn có ảnh được liên kết trong sổ làm việc Excel 2013, trên Cửa sổ 8, mũi tên xác thực dữ liệu có thể không xuất hiện trong ô hiện hoạt, trừ khi bạn đang nhấn nút chuột Như một giải pháp thay thế, hãy làm theo các bước sau để mũi tên xuất hiện
Panes đóng băngCài đặt Freeze Panes có thể gây ra sự cố với các mũi tên thả xuống, trong tất cả các phiên bản Excel. Có thêm sự cố trong Excel 97 trở về trước Trong bất kỳ phiên bản Excel nào, nếu danh sách thả xuống nằm trong ngăn cố định của cửa sổ Excel và cột bên phải đã được cuộn ra khỏi màn hình, thì mũi tên thả xuống sẽ không hiển thị Cảm ơn John Constable vì mẹo này Trong Excel 97, nếu danh sách thả xuống Xác thực dữ liệu nằm trong ngăn cố định của cửa sổ, mũi tên thả xuống sẽ không xuất hiện khi ô được chọn. Như một giải pháp thay thế, hãy sử dụng Window. Tách thay vì Cửa sổ. Panes đóng băng GHI CHÚ. Vấn đề này đã được sửa chữa trong các phiên bản sau
tham nhũngNếu không có giải pháp nào ở trên giải thích được các mũi tên thả xuống bị thiếu, trang tính có thể bị hỏng. Hãy thử sao chép dữ liệu vào một trang tính hoặc sổ làm việc mới và các mũi tên thả xuống có thể xuất hiện lại Hoặc, cố gắng sửa chữa tệp khi bạn mở nó
Đã bị xóa bởi MacroNếu bạn chạy một macro để xóa tất cả các hình trên trang tính, thì macro đó cũng có thể xóa mũi tên thả xuống để xác thực dữ liệu. Cảm ơn Ed Howland đã đề xuất thêm mẹo này Ví dụ: macro bên dưới xóa tất cả các hình dạng trên trang tính đang hoạt động
Macro an toàn. Để xóa các hình dạng khác một cách an toàn mà không xóa các mũi tên xác thực dữ liệu, hãy xem macro để xóa các đối tượng trên trang web của Ron de Bruin Sub DeleteShapesALL() 'WARNING: Deletes data val arrow ' if it is visible Dim sh As Shape Dim ws As Worksheet Set ws = ActiveSheet For Each sh In ws.Shapes sh.Delete Next sh End Sub Bài dự thi hợp lệ Không được phépNếu bạn nhập một mục nhập hợp lệ vào một ô có danh sách thả xuống, bạn vẫn có thể thấy thông báo lỗi cho biết "Giá trị bạn đã nhập không hợp lệ. " Ví dụ: danh sách này cho phép bạn chọn Có hoặc Không Tuy nhiên, nếu bạn gõ không, một thông báo cho biết điều đó không hợp lệ Gây ra. Danh sách được phân cáchBạn có thể thấy lỗi này nếu danh sách dựa trên danh sách được phân tách, được nhập vào hộp thoại Xác thực dữ liệu Sửa chữa. nhập chính xácDanh sách được phân cách có phân biệt chữ hoa chữ thường, vì vậy hãy nhập dữ liệu theo một trong những cách sau để ngăn chặn sự cố
Ví dụ: nếu bạn nhập Không, mục nhập sẽ được chấp nhận, không có thông báo lỗi, vì chữ cái đầu tiên là chữ hoa và chữ cái thứ hai là chữ thường Mục nhập không hợp lệ được phépNgay cả khi tạo các ô xác thực dữ liệu thả xuống, người dùng vẫn có thể nhập các mục nhập không hợp lệ Dưới đây là những lý do phổ biến nhất cho việc này. Bạn có thể lấy file mẫu ở phần download --Các ô trống trong danh sách nguồn --Thông Báo Lỗi Đã Tắt Các ô trống trong danh sách nguồnNếu danh sách nguồn là một phạm vi được đặt tên chứa các ô trống, người dùng có thể nhập bất kỳ mục nhập nào mà không nhận được thông báo lỗi. Video ngắn này cho thấy một giải pháp khả thi cho sự cố và có các bước được viết bên dưới video Ghi chú. Vấn đề này với các ô trống không xảy ra nếu danh sách nguồn là một địa chỉ phạm vi, e. g. $A$1. $A$10 Gây ra. Các ô trống trong phạm vi được đặt tênTrong ảnh chụp màn hình bên dưới, cột Người quản lý có một danh sách thả xuống với 5 tên Tuy nhiên, nếu một tên khác được nhập vào cột đó, sẽ không có cảnh báo lỗi. Tên Bill không có trong danh sách, nhưng được cho phép trong ô Điều này xảy ra khi một dải ô đã đặt tên được dùng làm nguồn danh sách và có một ô trống ở bất kỳ đâu trong dải ô đã đặt tên đó. Trong ví dụ này, có một ô trống ở cuối phạm vi được đặt tên, MgrList Để ngăn chặn các mục nhập không hợp lệ, nếu phạm vi được đặt tên có các ô trống
Cảnh báo lỗiNếu Cảnh báo lỗi bị tắt, người dùng sẽ có thể nhập bất kỳ mục nhập nào mà không nhận được thông báo lỗi Sửa chữa. Bật cảnh báo lỗiĐể bật cảnh báo
Nếu bạn sử dụng tính năng Khoanh tròn dữ liệu không hợp lệ trong Excel, đôi khi bạn có thể thấy các kết quả không mong muốn Video ngắn này cho thấy các sự cố tiềm ẩn với danh sách Thả xuống phụ thuộc, khi tính năng Bỏ qua trống bị tắt và tính năng Dữ liệu không hợp lệ trong vòng tròn được sử dụng Giới hạn mục trong danh sách thả xuốngCó giới hạn về số lượng mục sẽ hiển thị trong danh sách thả xuống xác thực dữ liệu
Nếu bạn cần nhiều mục hơn thế, bạn có thể tạo danh sách thả xuống phụ thuộc, được chia nhỏ theo danh mục. Có một tập tin mẫu trên trang này. Phụ thuộc thả xuống từ danh sách được sắp xếp Kích thước phông chữ thả xuống và độ dài danh sáchKhông thể thay đổi kích thước phông chữ xác thực dữ liệu và độ dài danh sách Cỡ chữ
Độ dài danh sách
GHI CHÚ; . Xem phần để biết cách giải quyết, để Danh sách nguồn trên các trang tính khác nhauGHI CHÚ. Điều này chỉ ảnh hưởng đến các phiên bản Excel cũ Trong các phiên bản Excel cũ, nếu bạn cố gắng tạo danh sách thả xuống xác thực dữ liệu Excel và tham chiếu đến danh sách nguồn trên một trang tính khác, thì bạn có thể thấy thông báo lỗi "Bạn không thể sử dụng các tham chiếu đến các trang tính hoặc sổ làm việc khác cho tiêu chí Xác thực dữ liệu. " Để tránh sự cố này, hãy sử dụng một trong những cách giải quyết sau Danh sách thả xuống có ký hiệuNếu danh sách nguồn có các ký hiệu từ phông chữ ký hiệu, chẳng hạn như Wingdings, thì các ký hiệu đó sẽ không xuất hiện chính xác trong danh sách thả xuống xác thực dữ liệu Gây ra. Phông chữ thả xuốngDanh sách thả xuống luôn hiển thị phông chữ Tahoma. Nó KHÔNG bị ảnh hưởng bởi định dạng trong cả hai
Sửa chữa. Sử dụng biểu tượng TahomaNếu bạn muốn hiển thị các ký tự biểu tượng trong danh sách thả xuống, hãy sử dụng các ký hiệu có sẵn trong phông chữ Tahoma, chẳng hạn như mũi tên, hình tròn và hình vuông Lấy file mẫu ở phần download Video này hiển thị các bước để hiển thị các biểu tượng trong danh sách thả xuống và hướng dẫn bằng văn bản ở bên dưới video Để tạo một danh sách các biểu tượng
Để tạo một danh sách thả xuống với các ký hiệu
Bạn có thể mở danh sách thả xuống bằng chuột hoặc bàn phím và bạn có thể cuộn qua danh sách bằng chuột hoặc phím tắt Hiển thị danh sách thả xuống
Cuộn qua các mục danh sáchCon chuột
bàn phím
Danh sách thả xuống trên trang tính được bảo vệCác ô có danh sách thả xuống không thể thay đổi nếu
GHI CHÚ. Trong Excel 2000 và các phiên bản cũ hơn,
Thả xuống và thay đổi sự kiệnTrong Excel 2000 và các phiên bản mới hơn, việc chọn một mục từ danh sách thả xuống Xác thực dữ liệu sẽ kích hoạt sự kiện Thay đổi. Điều này có nghĩa là mã có thể tự động chạy sau khi người dùng chọn một mục từ danh sách Để xem ví dụ, hãy truy cập trang Trang tính mẫu và bên dưới tiêu đề Bộ lọc, tìm Danh sách sản phẩm theo danh mục và tải xuống Danh sách sản phẩm. tập tin nén Trong Excel 97, việc chọn một mục từ danh sách thả xuống Xác thực dữ liệu không kích hoạt sự kiện Thay đổi, trừ khi các mục trong danh sách đã được nhập vào hộp thoại Xác thực dữ liệu. Trong phiên bản này, bạn có thể thêm một nút vào trang tính và chạy mã bằng cách bấm vào nút. Để xem ví dụ, hãy truy cập trang Bảng tính mẫu và bên dưới tiêu đề Bộ lọc, tìm Danh sách sản phẩm theo danh mục và tải xuống ProductsList97. tập tin nén Một tùy chọn khác trong Excel 97 là sử dụng sự kiện Tính toán để chạy mã. Để thực hiện việc này, hãy tham chiếu đến ô có xác thực dữ liệu trong công thức trên trang tính, e. g. =MATCH(C3,Danh sách danh mục,0). Sau đó, thêm mã bộ lọc vào sự kiện Tính toán của trang tính. Để xem ví dụ, hãy truy cập trang Trang tính mẫu và bên dưới tiêu đề Bộ lọc, tìm Danh sách sản phẩm theo danh mục và tải xuống ProductsList97Calc. tập tin nén. Làm cho thả xuống tạm thời rộng hơnTrình đơn thả xuống Xác thực dữ liệu là chiều rộng của ô chứa dữ liệu, tối thiểu là khoảng 3/4". Bạn có thể sử dụng sự kiện SelectionChange để tạm thời mở rộng cột khi cột đang hoạt động, sau đó thu hẹp cột khi bạn chọn một ô trong cột khác Ví dụ: với các ô Xác thực dữ liệu trong cột A Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Column = 1 Then Target.Columns.ColumnWidth = 20 Else Columns(1).ColumnWidth = 5 End If End Sub Để thêm mã này vào trang tính
Làm cho trình đơn thả xuống xuất hiện lớn hơnPhông chữ trong danh sách thả xuống xác thực dữ liệu là Tahoma, cỡ 8. Không có cài đặt nào trong Excel để làm cho cỡ chữ này lớn hơn, để dễ đọc hơn Nếu bạn giảm cài đặt thu phóng trên trang tính, vấn đề thậm chí còn tồi tệ hơn. Ví dụ: ảnh chụp màn hình này hiển thị danh sách thả xuống với cài đặt thu phóng là 80% Có một vài cách giải quyết mà bạn có thể sử dụng để làm cho phông chữ xác thực dữ liệu trông lớn hơn 1) Sử dụng macro để hiển thị hộp tổ hợp hoặc hộp danh sách 2) Tăng vĩnh viễn cài đặt thu phóng của trang tính (thủ công) 3) Tạm thời tăng cài đặt thu phóng bằng macro Sử dụng Combo Box hoặc ListBoxĐể làm cho các mục xác thực dữ liệu dễ đọc hơn, bạn có thể sử dụng lập trình, với hộp tổ hợp hoặc hộp danh sách, để hiển thị các mục. Phông chữ trong đó có thể được đặt thành bất kỳ kích thước nào và bạn cũng có thể đặt chúng để hiển thị nhiều hơn 8 mục mặc định cùng một lúc Sau đó, khi bạn nhấp đúp vào ô xác thực dữ liệu, hộp tổ hợp hoặc hộp danh sách sẽ xuất hiện và bạn có thể chọn từ đó. Xem hướng dẫn thêm hộp tổ hợp hoặc hiển thị hộp danh sách (có thể đặt chọn đơn hoặc chọn nhiều) Thay đổi cài đặt thu phóng vĩnh viễnNếu bạn không muốn sử dụng macro để điều chỉnh cài đặt thu phóng của trang tính, giải pháp thay thế này có thể thực hiện những gì bạn cần. Cảm ơn John Culley đã gợi ý phương pháp này Đây là ảnh chụp màn hình danh sách thả xuống với mức thu phóng là 100%. Tại hàng 2, các ô được định dạng bằng font chữ Cambria, cỡ chữ 12
Đây là cùng một trang tính với mức thu phóng là 120%. Phông chữ ở hàng 2 đã được giảm từ 12 xuống 10, vì vậy phông chữ trông có cùng kích thước như trước đây Thay đổi tạm thời cài đặt thu phóngĐể làm cho văn bản xuất hiện lớn hơn, bạn có thể sử dụng quy trình sự kiện để tăng cài đặt thu phóng khi ô được chọn. (Ghi chú. Kỹ thuật này có thể hơi giật cục) Có 3 ví dụ macro bên dưới -- Thu phóng khi một ô cụ thể được chọn -- Thu phóng khi một trong danh sách các ô cụ thể được chọn -- Thu phóng khi bất kỳ ô nào có danh sách xác thực dữ liệu được chọn Phóng to khi ô cụ thể được chọnNếu ô A2 có danh sách xác thực dữ liệu, đoạn mã sau sẽ thay đổi cài đặt thu phóng thành 120% khi ô đó được chọn Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$A$2" Then ActiveWindow.Zoom = 120 Else ActiveWindow.Zoom = 100 End If End Sub Để thêm mã này vào trang tính
Phóng to khi các ô cụ thể được chọnNếu một số ô có danh sách xác thực dữ liệu, đoạn mã sau sẽ thay đổi cài đặt thu phóng thành 120% khi bất kỳ ô nào trong số đó được chọn. Trong ví dụ này, các ô A1, B3 và D9 có xác thực dữ liệu Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Range("A1,B3,D9")) Is Nothing Then ActiveWindow.Zoom = 100 Else ActiveWindow.Zoom = 120 End If End Sub Phóng to khi bất kỳ ô nào có danh sách xác thực dữ liệu được chọnĐoạn mã sau sẽ thay đổi cài đặt thu phóng thành 120% khi bất kỳ ô nào có danh sách xác thực dữ liệu được chọn Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim lZoom As Long Dim lZoomDV As Long Dim lDVType As Long lZoom = 100 lZoomDV = 120 lDVType = 0 Application.EnableEvents = False On Error Resume Next lDVType = Target.Validation.Type On Error GoTo errHandler If lDVType <> 3 Then With ActiveWindow If .Zoom <> lZoom Then .Zoom = lZoom End If End With Else With ActiveWindow If .Zoom <> lZoomDV Then .Zoom = lZoomDV End If End With End If exitHandler: Application.EnableEvents = True Exit Sub errHandler: GoTo exitHandler End Sub Lấy các tập tin mẫuĐã chọn trống. Tệp mẫu này hiển thị một ví dụ trong đó một mục trống được chọn khi danh sách thả xuống mở ra. Xóa khoảng trống bằng tệp mẫu dải động Mục nhập không hợp lệ được phép. Sổ làm việc này có một ví dụ trong đó các mục nhập không hợp lệ có thể được nhập vào các ô có danh sách thả xuống. Xác thực dữ liệu Tệp mẫu mục nhập không hợp lệ Biểu tượng thả xuống. Sổ làm việc này có một ví dụ về danh sách thả xuống với các ký hiệu trong phông chữ Tahoma. Danh sách xác thực dữ liệu có ký hiệu Tại sao Excel của tôi liên tục báo tham chiếu không hợp lệ?Một trong những lý do lớn nhất khiến tham chiếu nguồn dữ liệu không hợp lệ xuất hiện lỗi Excel là sự hiện diện của “['or']”. Trong trường hợp đó, bạn chỉ cần đổi tên tệp và bỏ dấu ngoặc. Có thể bạn chưa lưu bảng tổng hợp trên ổ đĩa cục bộ của mình
Tại sao Excel không cho phép tôi chọn các ô?Để khắc phục sự cố này, hãy sử dụng một trong các phương pháp sau. Không bỏ chọn hộp kiểm Select Locked Cells khi bạn bảo vệ một trang tính . Khởi động Excel, mở sổ làm việc của bạn, rồi chọn phạm vi mà bạn muốn cho phép truy nhập.
Tại sao sao chép dán không hoạt động trong Excel?Đôi khi, chỉ cần mở lại sổ làm việc Excel có thể giải quyết các vấn đề liên quan đến sao chép và dán |