Как применить проверку данных, чтобы разрешить только числа в Excel?
Когда необходимо обеспечить ввод только числовых значений в определенные ячейки или диапазоны на вашем листе Excel — например, для идентификаторов, входных данных для расчетов, номеров счетов или оценок опросов — важно настроить ограничения, чтобы предотвратить случайный ввод текста, специальных символов или нежелательных знаков. Excel предоставляет несколько методов для достижения этого, каждый из которых имеет свои преимущества и варианты использования. В этом руководстве рассматриваются различные подходы к ограничению ввода так, чтобы принимались только числа, что поможет вам поддерживать точные и надежные данные.
Примените проверку данных, чтобы разрешить только числа в Excel
Примените код VBA, чтобы разрешить только числа в Excel
Легко разрешите только числа в диапазоне ячеек с помощью удивительного инструмента
Подход с использованием формул Excel для проверки только числовых данных
Больше уроков по проверке данных...
Примените проверку данных, чтобы разрешить только числа в Excel
Один из простых способов ограничить ввод в ячейки только числами — использовать встроенную функцию Проверки данных Excel в сочетании с пользовательской формулой. Этот метод особенно полезен для быстрого применения на стандартных листах Excel, не требует макросов и легко интегрируется в существующие документы. Вы можете применить его к одному или нескольким диапазонам, но имейте в виду, что он не предотвратит вставку пользователем нечисловых значений или ввод чисел в формате, который Excel интерпретирует как текст (например, с апострофом в начале). Следуйте этим шагам, чтобы настроить ограничение:
1. Выберите диапазон ячеек, где вы хотите ограничить ввод только числами. Например, если ваши данные будут вводиться в ячейки A2 до A12, выберите этот диапазон первым. Правильный выбор диапазона в начале помогает точно применять ограничения там, где это нужно, и избегать перезаписи существующих правил в других частях вашего листа.
2. Нажмите Данные > Проверка данных > Проверка данных. См. скриншот:
3. В диалоговом окне Проверка данных выполните следующие действия:
- 3.1 В раскрывающемся списке Разрешить выберите Пользовательский, чтобы определить персонализированное правило проверки.
- 3.2 Введите следующую формулу в поле Формула Эта формула проверяет, что запись в первой ячейке (например, A2) распознается Excel как число: =ЕЧИСЛО(A2)
- 3.3 Нажмите кнопку ОК, чтобы применить настройку проверки.
Примечание: Убедитесь, что A2 в формуле соответствует первой ссылке на ячейку в выбранном диапазоне. Если ваш диапазон начинается с другой ячейки, обновите формулу соответствующим образом (например, используйте =ЕЧИСЛО(B5), если ваш диапазон начинается с B5).
После этих шагов только числовые значения могут быть напрямую введены в указанные ячейки. Если пользователь попытается ввести буквы, символы или другое недопустимое содержимое, Excel предотвратит ввод и отобразит сообщение об ошибке. Имейте в виду, что если кто-то использует копирование–вставка для ввода запрещенных данных, этот метод может не предотвратить это. Также ввод, такой как число, сохраненное как текст (например, '123), не пройдет проверку. Для усиленного контроля над вставленными данными или для более сложных правил рассмотрите использование VBA или инструментов надстроек. Если вы хотите разрешить десятичные дроби или ограничить только целыми числами, используйте встроенные опции Целое число или Десятичная дробь в Проверке данных вместо «Пользовательский».
Совет по устранению неполадок: Если ваша проверка, кажется, не работает, убедитесь, что в целевом диапазоне нет конфликтующих правил проверки, и убедитесь, что ваша формула использует правильную относительную ссылку на ячейку для выбора диапазона. Если вы хотите отображать пользовательское сообщение при недопустимом вводе, нажмите вкладку Сообщение об ошибке в окне Проверка данных и настройте текст ошибки.
Примените код VBA, чтобы разрешить только числа в Excel
Для большей гибкости и перехвата вставленных, а также введенных данных использование макроса VBA является эффективным вариантом. Этот скрипт будет отслеживать заданный диапазон ячеек и очищать любую запись, которая не является числом, отображая предупреждение пользователю. Решения на основе VBA особенно подходят, если вы хотите обеспечить целостность ввода данных на более глубоком уровне или применить сложную логику. Имейте в виду, что для работы этого метода в вашей книге должны быть включены макросы VBA.
1. На листе, где вы хотите ограничить записи, щелкните правой кнопкой мыши вкладку листа внизу и выберите Просмотреть код в контекстном меню. Это открывает редактор Microsoft Visual Basic for Applications (VBA) для этого листа.
2. В окне Microsoft Visual Basic for Applications скопируйте следующий код VBA и вставьте его непосредственно в область кода для выбранного листа:
Код VBA: Разрешить только числа в диапазоне ячеек
Public mBol As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20191120
Dim xStrV As String
Dim xRg As Range
Dim xIRg As Range
Dim xFNum As Integer
On Error Resume Next
If Not mBol Then
Application.ScreenUpdating = False
Set xRg = Range("A2:A12")
If Not Intersect(xRg, Target) Is Nothing Then
xStrV = Target.Value
If Not IsNumeric(xStrV) Then
mBol = True
Target.Value = vbNullString
MsgBox "Only numbers are allowed in this range", , "Kutools"
End If
Application.ScreenUpdating = True
End If
Else
mBol = False
End If
End SubПримечание: Замените A2:A12 в строке Set xRg = Range("A2:A12") на ваш фактический диапазон ячеек, если вам нужно отслеживать другие ячейки. Этот код немедленно очистит любой нечисловой ввод и предупредит пользователя. Он также применяется к вставленным значениям. Однако, если несколько ячеек вставляются одновременно, код очистит их все, предотвращая вставку в целевую область. Это делает его наиболее эффективным для сценариев ввода в одну ячейку.
3. Чтобы закрыть редактор VBA и вернуться к вашему листу, нажмите Alt + Q. Теперь попробуйте вводить или вставлять в указанные ячейки: останутся только числа; любые другие вводы будут удалены и отображено сообщение. Если макрос не работает, убедитесь, что макросы включены, и код введен в окне кода конкретного листа, а не в общем модуле.
Совет: Вы можете дополнительно настроить сообщение или расширить логику для десятичных дробей, целых чисел или дополнительной обратной связи по мере необходимости. Если у вас есть несколько целевых диапазонов на разных листах, помните повторить вышеуказанные шаги в каждом соответствующем модуле рабочего листа.
Легко разрешите только числа в диапазоне ячеек с помощью удивительного инструмента
Для пользователей, ищущих экономящий время метод без формул или кодирования, Kutools для Excel предлагает утилиту Предотвращение ввода, которая может легко ограничить записи числами и заблокировать нежелательные символы всего за несколько кликов. Этот подход особенно хорошо работает при применении ограничений к нерегулярным диапазонам, смешанным данным или когда пользователи менее знакомы с встроенными настройками проверки данных Excel. Kutools также предоставляет четкие подсказки и дополнительные опции, делая его удобным и эффективным как для новичков, так и для опытных пользователей. Обратите внимание, однако, что эта утилита требует установки Kutools для Excel.
Перед применением Kutools для Excel, пожалуйста, сначала скачайте и установите его.
1. Выберите диапазон ячеек, для которого вы хотите разрешить только числовые вводы. Затем на ленте нажмите Kutools > Предотвращение ввода > Предотвращение ввода.
2. В диалоговом окне Предотвращение ввода выполните эти шаги:
- 2.1 Выберите опцию Разрешить ввод в эти символы;
- 2.2 В предоставленном текстовом поле введите 0123456789 (не включайте пробелы или знаки препинания). Это строго ограничивает ввод только цифрами от 0 до 9.
- 2.3 Нажмите кнопку ОК, чтобы применить это ограничение. См. скриншот:
3. Если в выбранном диапазоне уже есть существующая проверка данных, появится диалоговое окно Kutools для Excel, спрашивающее, хотите ли вы удалить текущие правила. Нажмите Да, чтобы продолжить и удалить существующие проверки, или Нет, чтобы отменить новую настройку. Подтверждение этого шага заменит старые проверки на новое правило, разрешающее только числа.
4. Появится диалоговое окно подтверждения, указывая, что новые ограничения вступили в силу и суммируя, какие числа разрешены. Нажмите ОК, чтобы завершить.
Теперь выбранный диапазон будет принимать только числа от 0 до 9 при вводе. Любая попытка ввести другие символы — такие как буквы, символы или пробелы — будет заблокирована в реальном времени. Пожалуйста, обратите внимание: Этот метод контролирует только вводимые данные, поэтому вставка содержимого, содержащего нечисловые символы, может быть не ограничена.
Примечание: Если позже вам понадобится отключить или изменить ограничение, вернитесь в Kutools > Предотвращение ввода и нажмите Очистить ограничения для вашего выбранного диапазона.
Если вы хотите воспользоваться бесплатной пробной версией (30-день) этой утилиты, пожалуйста, нажмите, чтобы скачать её, а затем перейдите к выполнению операции согласно вышеуказанным шагам.
Подход с использованием формул Excel для проверки только числовых данных
Помимо вышеуказанных методов, иногда вы можете захотеть выделить ячейки, содержащие нечисловые записи, для проверки, а не сразу блокировать ввод. Условное форматирование в сочетании с формулами Excel может визуально указывать нежелательные данные, делая этот подход идеальным для аудита, совместного ввода или сценариев очистки существующих данных. Это решение не является навязчивым и не блокирует записи, но привлекает внимание к ошибкам, чтобы облегчить ручную корректировку.
1. Выберите диапазон, где вы хотите выявить нечисловые записи, например, A2:A12.
2. Перейдите на вкладку Главная > Условное форматирование > Новое правило. В диалоговом окне выберите Использовать формулу для определения форматируемых ячеек.
3. Введите следующую формулу в поле:
=NOT(ISNUMBER(A2))Эта формула возвращает ИСТИНА для любой ячейки, которая не содержит число, вызывая выбранное форматирование.
4. Нажмите Формат, установите цвет или стиль выделения, а затем нажмите ОК для применения. Повторите для дополнительных диапазонов по мере необходимости.
Ячейки с нечисловым вводом в вашем целевом диапазоне теперь будут легко выявлены. Этот метод идеально подходит для выявления неправильного ввода без активного блокирования действий пользователя. Помните, эта альтернатива особенно эффективна для проверки существующих листов или как мягкий сигнал для пользователей в совместных средах.
Совет: Для более продвинутых проверок, таких как разрешение только положительных чисел или исключение десятичных дробей, вы можете изменить формулу. Например, =И(ЕЧИСЛО(A2);A2=ЦЕЛОЕ(A2);A2>0) выделит все ячейки, которые не являются положительными целыми числами.
Связанные статьи
Разрешить только формат даты в определенных ячейках
Как можно ограничить список ячеек, разрешая ввод только в формате даты в рабочем листе Excel? В этой статье мы поговорим о некоторых интересных приемах для решения этой задачи.
Проверка адресов электронной почты в столбце рабочего листа
Как всем известно, действительный адрес электронной почты состоит из трех частей: имени пользователя, символа «собака» (@) и домена. Иногда вы просто позволяете другим вводить только текст в формате адреса электронной почты в определенный столбец рабочего листа. Методы в этой статье сделают это возможным в Excel.
Примените проверку данных для принудительного формата номера телефона в Excel
Может быть, существует множество форматов номеров телефонов, которые могут использоваться при вводе в книгу Excel. Но как можно разрешить ввод только одного формата номера телефона в столбце рабочего листа? Например, я хочу, чтобы номер телефона был только в формате 123-456-7890. Методы в этой статье помогут вам.
Используйте проверку данных, чтобы разрешить ввод только номера социального страхования в Excel
Для записи номеров социального страхования всех сотрудников и принуждения их вводить номер социального страхования в формате xxx-xx-xxxx в столбце вы можете использовать функцию Проверки данных для решения проблемы.
Три метода проверки ячеек для принятия только IP-адресов в Excel
При использовании Excel, знаете ли вы, как настроить столбец или диапазон ячеек для принятия только формата IP-адреса (xxx.xxx.xxx.xxx)? Эта статья предоставляет несколько методов для решения этой задачи.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек