Как сделать ограничение на ввод данных в эксель
Обновлено: 03.07.2024
Лист Excel не всегда используется для обработки данных. Расположение строк и столбцов — это удобный способ представления и управления большим объемом информации, а функции Excel позволяют использовать электронную таблицу в качестве инструмента сбора данных.
Excel предлагает некоторые функции, которые позволяют вам использовать его как форму, которую могут заполнять другие пользователи, но если вам нужно ввести инструкции по заполнению формы в электронную таблицу, это будет беспорядочно.
Более простой способ убедиться, что вы получаете правильные данные, — это добавить лимиты и ограничения к ячейке. Любые данные или значения, выходящие за пределы лимита, будут отклонены.
Предел символов для ячеек в Excel
Вы можете установить ограничение на количество символов для ячеек в Excel. Это ограничение работает таким образом, что введенное значение должно содержать минимальное количество символов или не длиннее определенного количества символов.
Это полезно, когда пользователь должен ввести номер телефона, почтовый индекс или что-то подобное.
Добавить всплывающую подсказку для ограничения количества символов
Пользователь не сможет определить, есть ли в ячейке ограничение на количество символов, поэтому добавление всплывающей подсказки поможет им.
Вывод
Вы можете добавить верхний или нижний предел символов для ячеек или ввести точное количество символов, которое пользователь должен ввести, чтобы значение было принято. Просмотрите различные варианты и добавьте тип ограничения данных, который вам нужен для ячейки, столбца или строки.
Чтобы определить данные, которые допустимы к вводу, необходимо:
1. Выбрать ячейку, которую нужно проверить.
2. Выбрать команду Проверка данных в группе Работа с данными вкладки Данные. В диалоговом окне Проверка вводимых значений открыть вкладку Параметры.
3. Определить необходимый тип проверки.
Чтобы разрешить ввод значений, которые находятся в заданных пределах. В списке Тип данных необходимо выбрать вариант Целое число или Действительное. В списке Значения надо задать необходимое ограничение. Например, чтобы установить нижнюю и верхнюю границу, следует выбрать значение между. Задать минимальное, максимальное или определено допустимое значение (рис. 13).
Рисунок 13. - Диалоговое окно Проверка вводимых значений. Условие проверки.
Рисунок 14. – Окно проверки вводимых значений.
Работа с формулами
Формулы представляют собой выражение, по которым выполняются вычисления на странице. Формула начинается со знака равенства (=). Ниже приведен пример формулы, который умножает 2 на 3 и добавляет к результату 5.
Формула также может включать следующие элементы: функции, ссылки, операторы и константы. = ПИ()*А2^5
Элементы формулы:
1. Функции. Функция ПИ() возвращает значение числа π: 3,142
2. Ссылка (или имена). A2 возвращает значение ячейки A2.
3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например, 5.
4. Операторы. Оператор ^ возвдит число в степень, а звездочка (*) выполняет умножение. В некоторых случаях может потребоваться использование функции как одного из аргументов другой функции. Например, в следующей формуле функция СРЗНАЧ вложена в функцию ЕСЛИ для сравнения среднего значения нескольких значений с числом 50.
= ЕСЛИ (СРЗНАЧ (F2: F5)> 50; СУММ (G2: G5), 0)
Ограничение количества уровней вложения функций. В формулах можно использовать до семи уровней вложения функций. Когда функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, функции СРЗНАЧ и СУММ считаются функциями второго уровня, так как обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня и так далее.
Чтобы вставить функцию в формулу, установите указатель на позицию, в которую следует ввести формулу, и нажмите кнопку мыши.
1. На вкладке Формулы в группе Библиотека функций выбрать команду Вставить функцию.
2. В списке Категория выберите категорию функции.
3. В списке Функция выберите необходимую функцию.
5. Введите величины, ссылки, имена, формулы и функции в поля аргументов.
Рисунок. 15. - Мастер вставки функций
Условное форматирование
Если необходимо, чтобы ячейка меняла формат, который ей задан (заливка, шрифт, начертание, рамки и т.д.) при выполнении определенного условия, нужно:
1. Выделить ячейки, которые должны автоматически менять свой цвет.
2. Выбрать на вкладке Главная группы Стили команду Условное форматирование(рис. 16).
Рисунок 16. – Команда Условное форматирование.
В открывшемся окне можно задать условия и параметры форматирования ячейке при выполнении условия. Подробнее это можно рассмотреть выбрав команду Создать правило, и тем самым открыв диалоговое окно Создание правила форматирования (рис.17).
Рисунок. 17 - Диалоговое окно Создание правила форматирования.
Изменяя тип правила можно форматировать не только ячейки, но и значение, для которых выполняется приведенное условие (рис.18).
Рисунок 18. – Форматирование значений.
Если для диапазона ячеек заданные критерии условного форматирования, то нельзя отформатировать эти ячейки через меню Формат ячеек в ручную. Чтобы вернуть эту возможность надо удалить условия в окне Условное форматирование с помощью команды Удалить правила.
Рисунок 19. – Команда Удалить правила.
Порядок выполнения работы:
1. Подготовить и создать таблицу согласно варианту, указанному в индивидуальном задании.
2. Наложить ограничения по введению данных.
3. Определить основные функции для выполнения задачи и сформировать формулы для вычисления.
4. Провести вычисления по заданным условиям.
Варианты заданий
Вариант №1
1. Подготовить и создать таблицу:
| Сведения об успеваемости студентов гр. КН-15 | |||||||||
| № п/п | Ф.И.О. | Математика | Программирование | Системотехника | Ин. Язык | КИТ | Пакеты | Средний балл | Стипендия |
| Малов С.Ю. | |||||||||
| Величко Д.С. | |||||||||
| Возняк А.М. | |||||||||
| Мальцев А.Ю. | |||||||||
| Иванченко А.В. | |||||||||
| Липчик А.Г. | |||||||||
| Малышко А.Н. | |||||||||
| Волков Н.В. | |||||||||
| Матюшенко А.А. | |||||||||
| Бурлака А.А. | |||||||||
| Дата (текущая дата): | 22.02.2015 |
2. На ячейках по предметам наложить ограничения по введению от 2 до 5.
3. Средний бал по каждому студенту вычислить как среднее арифметическое оценок, больших чем "2". Если есть оценка по предмету "2", то средний бал не рассчитывается и не выводиться.
4. Стипендию вычислить согласно условиям 4,5 4000 - 20%, иначе 15%.
7. Посчитать сколько выплат более 4000, выделить их автоматически другим цветом.
8. Вывести текущую дату и время.
Вариант №6
1. Подготовить и создать таблицу:
| Начисление стипендии | |||||||
| № п/п | Ф.И.О. | Математика | Программирование | Системотехника | КИТ | Средний балл | Стипендия |
| Бурлака А.А. | |||||||
| Величко Д.С. | |||||||
| Возняк А.М. | |||||||
| Волков Н.В. | |||||||
| Иванченко А.В. | |||||||
| Липчак А.Г. | |||||||
| Мальцев А.Н. | |||||||
| Малов С.Ю. | |||||||
| Матюшенко А.А. | |||||||
| Марченко В.В. | |||||||
| 22.02.2015 |
2. На ячейках по предметам наложить ограничения по введению от 30 до 100.
3. Балы в национальной шкале посчитать по следующим условиям: 2 плата равна 1,26 грн.; тепло - за 1 м 2 равна 4,87 грн
5. Расчетные столбцы 8, 9 зависят от количества людей, проживающих в квартире. Плата за газ на одного человека составляет 5,21 грн, за воду - 26,54 грн.
6. Если в квартире льготник вида 1, то за него по всем платежам, кроме квартиры снимается 25%. Если льготник типа 2, то за него, включая плату за квартиру, снимается по 50%.
7. Определить цвет для ячеек столбца 11 в зависимости от типа льготы.
1. Подготовить и создать таблицу:
| Адрес | Количество проживающих | Льгота % | Площадь квартиры кв.м. | Оплата отопления | Горячая вода | Общая сумма платежа за тепловую энергию |
| Начальные показания | Конечные показания | Разность | Сумма платежа за горячую воду | |||
| ул.Артема,д.10, кв.10 | ||||||
| ул.Артема,д.10, кв.11 | ||||||
| ул.Артема,д.10, кв.12 | ||||||
| ул.Артема,д.10, кв.13 | ||||||
| пр.Мира,д.2, кв.1 | ||||||
| пр.Мира,д.2, кв.2 | ||||||
| ИТОГО | ||||||
| Дата составления расчета |
3. Разрешить вводить пользователю в ячейки 2,3,4 только целые числа.
4. Плата за отопление зависит от площади квартиры (цена за 1 м 2 соответствует 41,2 грн).
5. Процент льготы снижает оплату на 25% или на 50%, но только для одного человека.
6. Определить цвет для ячеек столбца 10. Если общая плата лежит в диапазоне от 500 до 800 грн, то ячейка должна иметь желтый цвет, если плата менее 500 - зеленый, более 800 - розовый.
7. Сортировать данные в таблице по столбцу 4.
8. Посчитать количество квартир с каждым типом льготы.вести дату составления отчета.
1. Подготовить и создать таблицу:
| Учет выполненных работ по ремонту компьютерной техники | |||||||
| Дата заказа | Работа | Номер заказа | Категория работы | План | Факт | Цена работы, грн | Сумма оплаты, грн |
| Начало | Окончание | Начало | Окончание | ||||
| 12.11.09 | Заправка картриджа принтера | 13.11.09 | 13.11.09 | 13.11.09 | 13.11.09 | 70,00 | |
| 12.11.09 | Диагностика материнской платы | 13.11.09 | 13.11.09 | 13.11.09 | 15.11.09 | 120,00 | |
| 12.11.09 | Замена картриджа принтера | 13.11.09 | 13.11.09 | 13.11.09 | 13.11.09 | 70,00 | |
| 12.11.09 | Диагностика материнской платы | 14.11.09 | 15.11.09 | 14.11.09 | 17.11.09 | 120,00 | |
| 12.11.09 | Замена видео карты | 14.11.09 | 15.11.09 | 14.11.09 | 15.11.09 | 100,00 | |
| 13.11.09 | Замена звуковой карты | 13.11.09 | 13.11.09 | 13.11.09 | 16.11.09 | 100,00 | |
| 13.11.09 | Прочистка системного блока | 14.11.09 | 15.11.09 | 14.11.09 | 15.11.09 | 80,00 | |
| 13.11.09 | Заправка картриджа принтера | 15.11.09 | 16.11.09 | 15.11.09 | 17.11.09 | 70,00 |
2. На ячейки начала и окончания плановых и фактических работ наложить ограничения по введению данных: - Все даты должны позже даты заказа и не позднее даты одного месяца спустя; - Дата и время окончания работы должна быть больше чем дата ее начала.
3. Значения в столбцах 2 и 3 должны быть только целыми .
4. Сумма платы за работу зависит от своевременности ее выполнении и ее категории. Если задержка выполнения работы есть, но не превышает 1 дня, то работа становится дешевле на 5%. Если превышает от 1 дня до 3 дней – то для работ категории 2 на 20%, а для работ категории 3 на 15%. Если задержка более 3 дней, то на 50% для категории 1, 2, и на 30% для категории 3.
5. Посчитать количество работ каждой категории.
6. Отсортировать по категориям, а в них по величине оплаты.
Вариант №11
1. Подготовить и создать таблицу:
| № п/п | Ф.И.О. | Средний балл при поступлении | Нейросетевые технологии | Параллельные вычисления | Нечеткая логика | Корпоративные системы | Средний балл за семестр | Общий средний балл | Стипендия |
| Андреев С.Ю. | 5,0 | ||||||||
| Величко Д.С. | 3,7 | ||||||||
| Возняк А.М. | 4,5 | ||||||||
| Малов С.Ю. | 5,0 | ||||||||
| Иванченко А.В. | 4,9 | ||||||||
| Липчик А.Г. | 4,8 | ||||||||
| Кузнецов А.Н. | 3,9 | ||||||||
| Волков Н.В. | 4,2 | ||||||||
| Матюшенко А.А. | 4,3 | ||||||||
| Бурлака А.А. | 4,0 | ||||||||
| Дата (текущая дата): | 22.02.2015 |
2. На ячейках по предметам 4, 5, 6, 7 наложить ограничения по введению целых чисел от 2 до 5.
3. Средний бал за семестр по каждому студенту вычислить как среднее арифметическое оценок, больших чем "2". Если оценка по предмету "2", то средний бал не рассчитывается и не выводиться.
4. Стипендию вычислить согласно условиям 4,5
Что входит в перечень работ по подготовке дома к зиме: При подготовке дома к зиме проводят следующие мероприятия.
Причем ввести символы (буковки или циферки) можно только в одну ячейку в этой строке (после цифры "1" по горизонтали) , а не вводятся символы только в одну строку, которая под нумерацией (см. скриншот) .
Вышестоящая организация говорит, что у них всё работает, мол это у Вас что-то с компьютерами!
Но не со всеми же, включая и домашние компы сотрудников!
Условие проверки
В редакторе Excel существует возможность ограничить ввод информации по каким-нибудь критериям. Процесс происходит следующим образом.
- После этого появится окно, в котором можно будет выбрать формат информации для проверки.
Рассмотрим эти форматы более внимательно.
Любое значение
В этом случае в ячейку можно будет вводить что угодно. Параметр используется по умолчанию для любой клетки.
Целое число
Выбрав этот формат, вы сможете отредактировать дополнительную настройку во втором поле.
Действительное
Список
Этот формат наиболее интересный.
Для того чтобы понять его возможности, нужно выполнить несколько простых действий.
- Заполните чем-нибудь несколько клеток. Неважно чем.
- Благодаря этому теперь в этой ячейке можно выбирать нужное слово из выпадающего списка. Это намного удобнее, чем изменять текст вручную.
Данный формат подходит для тех случаев, когда ячейка должна содержать только дату. При этом доступны точно такие же дополнительные условия, как с целыми и действительными числами.
Время
Аналогично и тут. Только здесь указывается одно время (без даты).
Длина текста
Иногда бывают ситуации, когда необходимо ограничить не формат клетки, а длину содержимого выражения. То есть, благодаря этой настройке, вы можете сделать так, чтобы в определенных полях можно было указать текст не больше нужного количества символов. Например, в графе номер телефона или название города.
Вариантов довольно много. Этот способ используется при заполнении каких-нибудь бланков или анкет.
Другой
Данный формат отличается от всех остальных. Здесь вы можете указать какую-нибудь формулу для проверки соответствия информации какому-либо условию.
При желании вы можете добавить какое-нибудь уведомление о допущенной ошибке при вводе данных в ячейку. Для это вам нужно выполнить несколько простых операций.
В качестве примера мы укажем следующие настройки.
- Введите любое число. Например, что-нибудь меньше указанного значения. Нажмите на клавишу Enter . И вы увидите уведомление о том, что была допущена ошибка ввода данных.
А теперь попробуйте убрать введенные настройки и оставить пустые поля.
В результате этого вы и увидите указанную ранее ошибку. То есть, если ничего не указывать вручную, редактор выведет предупреждение по умолчанию.
Отличие версий Microsoft Excel
Описанная выше инструкция подходит для современных редакторов 2010, 2013 и 2016 годов. По сравнению со старыми программами существуют некоторые отличия.
В старом Excel 2003 ошибка точно такая же.
Чтобы у пользователей после ввода информации подобных ошибок не возникало, необходимо указывать такие подсказки заранее. Делается это очень просто.
- Теперь каждый раз, когда бы будете активировать эту ячейку, будете видеть подобную подсказку. Это значительно облегчит ситуацию другим пользователям, которые будут работать с созданными вами файлами.
Как отключить эту ошибку
- Теперь можно вносить любые данные, словно вы открыли пустой файл и никаких настроек там нет.
Примеры от компании Microsoft
Если вы не совсем понимаете, каким образом нужно указывать условия для ввода значений, рекомендуется открыть официальный сайт компании Майкрософт. Там вы сможете найти файл примеров. В нём указаны абсолютно все типы данных и различные случаи составления документов и отчётов.
Заключение
В данной статье мы рассмотрели, в каких случаях может появляться ошибка ввода значений в таблицах Excel. Кроме этого, особое внимание было уделено настройкам различных условий для определения допустимого набора величин.
Если у вас что-то не получается, значит, вы что-то упускаете из виду или выбираете не тот формат данных. Попробуйте повторить эти действия более внимательно.
Видеоинструкция
Если вдруг вы не смогли понять данную инструкцию, ниже прилагается видеоролик, в котором все шаги описываются еще более детально с дополнительными пояснениями.
Файлы Microsoft Excel довольно часто используются для хранения данных, с которыми работает несколько различных пользователей. Иногда пользователи ошибаются и вводят некорректные данные, из за чего возникают проблемы различной степени сложности, в зависимости от важности данных. Однако подобной ситуации можно легко избежать.
Специально на такой случай в Excel есть возможность проверять корректность данных, записываемых в определенные ячейки. При вводе недопустимых данных Excel будет выдавать предупреждение или запрет на продолжение операции.
В качестве примера укажем любое целое число не больше 100000.
Эта подсказка будет появляться рядом с ячейкой при ее выборе и оставаться на экране до тех пор, пока вы не перейдете к другой ячейке.
Подобные несложные действия облегчат жизнь пользователям и помогут избежать многих проблем при совместной работе с данными в excel.
Выделите диапазон, который следует проверять. Можно выбрать произвольные массивы, все ячейки листа, отдельные строки или столбцы. Жмем на ленте Данные – Работа с данными – Проверка данных .
- Целое число
- Действительное
- Дата
- Время
Однако, для любого из этих вариантов придется выбрать ограничение по величине. Например, целое число от 3 до 10:
Если вам так и хотелось, можно на этом остановиться. А что, если не нужно проверять величину, а только формат? Чтобы вводить любые числа, без исключений?
Проверка на ввод числа без контроля значения
Действуем по порядку:
Теперь, при попытке записи в ячейку нечислового значения, программа не даст этого сделать.
Читайте также: