Как настроить проверку данных, чтобы разрешить ввод только чисел в Excel?
Когда требуется разрешить ввод только числовых значений в определённые ячейки или диапазоны листа Excel — например, для идентификаторов, исходных данных расчётов, номеров счетов или баллов опроса — крайне важно настроить ограничения, исключающие случайный ввод текста, специальных символов или нежелательных знаков. Excel предлагает несколько методов для решения этой задачи, каждый из которых обладает своими преимуществами и подходит для конкретных ситуаций. В этом руководстве рассматриваются различные подходы к ограничению ввода, позволяющие принимать исключительно числа, что обеспечит точность и надёжность ваших данных.
Применение проверки данных для разрешения ввода только чисел в Excel
Применение кода VBA для разрешения ввода только чисел в Excel
Простое разрешение ввода только чисел в диапазоне ячеек с помощью мощного инструмента
Подход с использованием формулы Excel для проверки только числовых значений
Дополнительные руководства по проверке данных…
Применение проверки данных для разрешения ввода только чисел в Excel
Один из простых способов ограничить ввод в ячейках только числами — использовать встроенную функцию Excel «Проверка данных» вместе с пользовательской формулой. Этот метод особенно удобен для быстрого применения на стандартных листах Excel: он не требует макросов и легко интегрируется в существующие документы. Его можно применять к одному или нескольким диапазонам, но имейте в виду, что он не помешает пользователю вставить нечисловые значения или ввести числа в формате, который Excel интерпретирует как текст (например, с начальным апострофом). Выполните следующие шаги, чтобы настроить такое ограничение:
1. Выделите диапазон ячеек, в которые разрешён ввод только чисел. Например, если данные будут вводиться в ячейки с A2 по A12, сначала выделите именно этот диапазон. Правильный выбор диапазона на начальном этапе обеспечивает точное применение ограничений именно там, где это нужно, и помогает избежать случайной перезаписи существующих правил в других частях листа.
2. Нажмите Данные > Проверка данных > Проверка данных. См. снимок экрана:
3. В диалоговом окне Проверка данных выполните следующие действия:
- 3,1 В списке Разрешить выберите значение Пользовательский, чтобы задать собственное правило проверки.
- 3,2 Введите следующую формулу в поле Формула. Эта формула проверяет, распознаёт ли Excel значение в первой ячейке (например, A2) как число: =ISNUMBER(A2)
- 3,3 Нажмите кнопку OK, чтобы применить настройки проверки.
Примечание: Убедитесь, что ссылка на ячейку A2 в формуле соответствует первой ячейке вашего диапазона «Выберите диапазон». Если ваш диапазон начинается с другой ячейки, обновите формулу соответствующим образом (например, используйте =ISNUMBER(B5), если диапазон начинается с B5).
После выполнения этих шагов в указанные ячейки можно будет вводить только числовые значения. Если пользователь попытается ввести буквы, символы или другой недопустимый контент, Excel заблокирует ввод и отобразит сообщение об ошибке. Имейте в виду: при копировании и вставке запрещённых данных этот метод может не сработать. Кроме того, данные, введённые как текст (например, '123), не пройдут проверку. Для более строгого контроля над вставляемыми данными или реализации сложных правил рекомендуется использовать VBA или надстройки. Если вы хотите разрешить десятичные дроби или ограничить ввод только целыми числами, используйте встроенные параметры проверки данных Целое число или Десятичное вместо пункта «Пользовательский».
Совет по устранению неполадок: если проверка не работает, убедитесь, что в целевом диапазоне не действуют конфликтующие правила проверки, и проверьте, правильно ли в формуле указана относительная ссылка на ячейку для выделенного диапазона. Чтобы отображать собственное сообщение при недопустимом вводе, перейдите на вкладку Предупреждение об ошибке в окне «Проверка данных» и настройте текст ошибки.
Применение кода VBA для разрешения ввода только чисел в Excel
Для максимальной гибкости и надёжного контроля как вводимых, так и вставляемых данных оптимальным решением станет использование макроса 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 Нажмите кнопку OK, чтобы применить это ограничение. См. снимок экрана:
3. Если для выбранного диапазона уже задана проверка данных, появится диалоговое окно Kutools для Excel, предлагающее удалить текущие правила. Нажмите Да, чтобы продолжить и удалить существующие правила проверки, или Нет, чтобы отменить новую настройку. Подтверждение этого шага заменит старые правила новым правилом, допускающим только числа.
4. Появится диалоговое окно подтверждения, информирующее, что новые ограничения вступили в силу, и кратко описывающее допустимые значения. Нажмите ОК, чтобы завершить.
Теперь выбранный диапазон будет принимать только цифры от 0 до 9 при вводе с клавиатуры. Любая попытка ввести другие символы — например, буквы, знаки препинания или пробелы — будет мгновенно блокироваться. Обратите внимание: этот метод контролирует исключительно ввод с клавиатуры, поэтому вставка данных, содержащих нечисловые символы, может остаться без ограничений.
Примечание. Если позже потребуется отключить или изменить ограничение, вернитесь в меню Kutools > Ограничить ввод и нажмите Очистить ограничения для выбранного диапазона.
Если вы хотите воспользоваться бесплатной пробной версией (30 дней) этой утилиты, нажмите, чтобы скачать её, а затем выполните операцию в соответствии с приведёнными выше шагами.
Подход с использованием формул Excel для проверки ввода только чисел
Помимо описанных выше методов, иногда гораздо эффективнее не блокировать ввод сразу, а выделять ячейки с нечисловыми значениями для дальнейшего анализа. Условное форматирование в сочетании с формулами Excel позволяет наглядно обозначать нежелательные данные — идеальное решение для аудита, совместного ввода или очистки уже существующих данных. Такой подход не мешает вводу, но привлекает внимание к ошибкам, упрощая их ручное исправление.
1. Выделите диапазон, в котором нужно найти нечисловые значения, например, A2:A12.
2. Перейдите на вкладку Главная > Использовать условное форматирование > Создать правило. В диалоговом окне выберите пункт Использовать формулу для определения форматируемых ячеек.
3. Введите в поле следующую формулу:
=NOT(ISNUMBER(A2))Эта формула возвращает ИСТИНУ для любой ячейки, не содержащей число, и активирует заданное форматирование.
4. Нажмите кнопку Формат, задайте цвет или стиль выделения, а затем нажмите ОК, чтобы применить настройки. При необходимости повторите это действие для других диапазонов.
Теперь ячейки с нечисловыми значениями в указанном диапазоне будут легко выявляться. Этот метод идеально подходит для обнаружения некорректного ввода без блокировки действий пользователя. Особенно эффективен он при анализе уже заполненных листов или в качестве мягкого напоминания участникам совместной работы.
Совет: Для более сложных проверок — например, чтобы разрешать только положительные числа или исключать десятичные значения, — можно изменить формулу. Например, формула =AND(ISNUMBER(A2),A2=INT(A2),A2>,0) выделит все ячейки, не содержащие положительные целые числа.
Связанные статьи
Разрешить ввод только даты в определённых ячейках
Как ограничить определённые ячейки на листе Excel, чтобы в них можно было вводить данные только в формате даты? В этой статье мы расскажем о нескольких эффективных способах решения этой задачи.
Можно вводить только адреса электронной почты в столбце листа
Как известно, корректный адрес электронной почты состоит из трёх частей: имени пользователя, символа «собаки» (@) и домена. Иногда требуется разрешить другим пользователям вводить данные только в формате адреса электронной почты в указанном столбце листа. Методы, описанные в этой статье, помогут реализовать это в Excel.
Как с помощью проверки данных задать строгий формат ввода номера телефона в Excel
При вводе данных в рабочую книгу Excel пользователи часто используют разные форматы номеров телефона. Но как разрешить ввод только в одном конкретном формате — например, 123-456-7890? Методы из этой статьи помогут вам легко решить эту задачу.
Использование проверки данных для разрешения ввода только номеров SSN в Excel
Чтобы собирать номера SSN всех сотрудников и обеспечить их ввод в формате 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек