Как сделать проверку данных в excel?
При заполнении таблиц в Экселе вручную приходится вводить очень много данных, что может привести к появлению множества опечаток и ошибок. Некоторые такие ошибки может исключить проверка данных в Excel при вводе, что мы далее и рассмотрим.
Сразу стоит отметить, что проверка вводимых данных в Excel будет работать только при вводе этих данных с клавиатуры. При вставке информации из буфера обмена или другого источника проверка не выполняется. Для уже введенных значений все-же можно произвести проверку, что приведет к обведению неверных данных красным овалом, но об этом далее.
Чтобы сделать проверку данных в Excel для определенных ячеек, их необходимо выделить, перейти на вкладку «Данные», и найти в разделе «Работа с данными» меню «Проверка данных». При нажатии на стрелочку справа от данного пункта появляется три пункта меню, из которых нам нужен самый первый «Проверка данных…».
В появившемся окошке «Проверка вводимых значений» настраивается проверка. На вкладке «Параметры» выбирается из списка тип данных, которые будут проверяться и вводиться в выделенный нами диапазон ячеек. Для примера выберем «Целое число».
После выбора типа данных становится возможным выбор условия соответствия вводимого значения в графе «Значение:». Выберем для примера «Между».
Далее в графах «Минимум:» и «Максимум:» необходимо указать значения, или указать на ячейки с данными значениями, нажав на соответствующий значок справа от каждой графы ввода.
На следующей вкладке «Сообщение для ввода» можно указать заголовок и само сообщение подсказку, которое будет высвечиваться при активации ячейки с проверкой вводимых данных.
Для проверки уже введенных или вставленных через буфер обмена значений, можно выбрать пункт меню «Обвести неверные данные», после чего неудовлетворяющие условию значения будут обведены красным овалом. После ввода правильного значения овал автоматически исчезает.
Как в Excel сделать проверку данных в ячейках
Пример прайс-листа с введенными ошибками в процентных ставках НДС:
Чтобы в Excel сделать проверку вводимых данных в ячейки следует выполнить ряд последовательных действий:
- Выделите диапазон ячеек где вводиться формула с процентной ставкой для цен с НДС и выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
- В появившемся окне «Проверка вводимых значений» на вкладке «Параметры» из выпадающего списка «Тип данных:» выберите опцию «Список».
- В полю ввода «Источник:» введите значения разных процентных ставок: 0%; 20%; освобождается.
- Перейдите на закладку «Сообщение об ошибке» и заполните текстовое поле «Сообщение:» текстом который будет содержать сообщение при вводе других значений, которые отличаются от указанных значений в списке.
Закладка «Сообщение об ошибке» предоставляет пользователю возможность оформить стиль сообщения об ошибочных вводах значений. Если пользователь вводить в ячейку неправильное значение тогда будет выполнен один из 3-х параметров:
- Останов – данный параметр разрешает вводить только правильное значение, выбранного из выпадающего списка или отменить ввод оставив пустую ячейку.
- Предупреждение – этот параметр предупреждает об вводе ошибочного значения и предоставляет попытку отредактировать или выбрать из выпадающего списка.
- Сообщение – параметр сообщает о неправильном значении и позволяет проигнорировать ошибку.
После заполнения всех параметров в окне «Проверка вводимых значений» нажмите на кнопку ОК.
В результате возле каждой заполняемой ячейки справа появляется стрелка выпадающего списка, в которой указана правильная процентная ставка НДС.
Внимание! Инструмент «Проверка данных» срабатывает только при вводе значений в ячейку. Если же значения буду скопированы из других ячеек, то при вставке этот инструмент удалиться из этих ячеек и не сработает.
Привет всем. Как часто Вы сталкиваетесь с таблицами общего пользования, которые заполняют несколько сотрудников? Например, Вы формируете базу данных в Эксель, и всю информацию вносит несколько человек. Ошибок не избежать, некорректные данные часто проскакивают в таблицы. Вот если бы Excel мог проверить их перед внесением и указать на ошибку…
А он может! В программу встроен мощный инструмент под названием «Проверка данных», который минимизирует ошибки внесения информации.
Вы можете предусмотреть проверку данных еще на этапе проектировки документа, чтобы пользователь не мог внести ошибочные данные, или добавить проверку к уже существующим массивам.
Чтобы подключить инструмент «Проверка данных», выделите проверяемые ячейки и кликните на ленте «Данные – Работа с данными – Проверка данных». Откроется окно настройки «модератора»:
На вкладке «Параметры» нужно задать условие для проверки в поле «Тип данных». Есть 8 типов условий для проверки:
- Любое значение – проверка данных отключена
- Целое число – можно ввести лишь целое число с дополнительным критерием;
- Действительное число – любое действительное число с дополнительным критерием
- Список – выбрать значение из заранее заданного списка
- Дата – ввести только дату, удовлетворяющую дополнительному критерию
- Время – ввести время с дополнительным условием
- Длина текста – ввести текст определенной длины
- Другой – ввести значение, которое удовлетворяет записанному вручную логическому выражению
- Игнорировать пустые ячейки – не применять условие к пустым ячейкам
- Распространить изменения на другие ячейки с тем же условием – найти ячейки с аналогичными условиями и применить к ним такие же изменения
На вкладке «Сообщение для ввода» введите подсказку о том, какие данные нужно внести в ячейку.
На вкладке «Сообщение об ошибке выберите событие, которое произойдет при введении неправильных данных. Сначала сделаем выбор в списке «Вид»:
- Останов – отменить внесение ошибочных данных, вывести заданное сообщение
- Предупреждение – программа сообщит, что значение не соответствует требованиям и уточнит, вносить ли его в ячейку
- Сообщение – примерно то же, что и предупреждение, но выглядит менее устрашающе
После этого запишем заголовок и основной текст для окна-предупреждения.
А теперь поговорим детальнее о каждом из видов условий.
Условие на целое число в Excel
Если в ячейки можно вносить только целые числа – выберите этот тип данных. Станет активным список с дополнительным параметром, в котором нужно задать ограничения. Сделайте выбор:
- Между минимальным и максимальным значением
- Вне заданного промежутка значений
- Равно определенному значению
- Не равно определенному значению
- Больше заданного значения
- Меньше заданного значения
- Больше или равно значению
- Меньше или равно значению
Условие на действительное значение
Действительные числа – все положительные, отрицательные числа, а так же, ноль. От предыдущего пункта это правило отличается тем, что можно вводить и дробные числа. Все остальные настройки – аналогичны описанным выше.
Список
Чтобы задать список, опишите его в поле источник. Это можно сделать несколькими способами:
- Записать перечень в ячейках на листе книги, а в поле «Источник» указать ссылку на этот массив
- Создать именованный массив, в поле «Источник» указать его имя
- Перечислить пункты прямо в поле «Источник» через точку с запятой
Пользователь может написать и свое значение, отличное от тех, что в списке. Но, если выбран тип проверки «Останов», программа не позволит внести его в ячейку. При других способах проверки, значение будет записано в ячейку, но все равно будет считаться неверным.
Этот фильтр позволит внести в ячейку только дату. Напомню, что дата в Эксель дата – это тоже число, поэтому такое правило перекликается с условием на действительное значение. Тем не менее, ввести можно только дату, с указанием параметров, таких же, как и в первых двух пунктах.
Время
Проверка на время – то же, что и в предыдущем пункте, только задается конкретное время без привязки к дате. Так же, вводится дополнительный параметр для ограничения выбора.
Длина текста
С этой проверкой Вы можете ввести и число и текст, но предлагается задать условие на его длину (в символах). Список параметров все тот же: длина между заданными значениями, вне промежутка, равна какой-то величине и т.п.
Другой
Этот способ проверки самый гибкий. В строке «Формула» Можно задать свою логическую формулу для проверки, если ни один из предыдущих способов проверки Вас не устраивает. Например, чтобы в ячейку А1 вносили только коды, начинающиеся с символов “SUV”, и содержащие 12 символов, внесем в поле формулу: =И(ЛЕВСИМВ(A1;3)=»SUV»;ДЛСТР(A1)=12). Лишь при выполнении этого правила значение будет считаться верным.
Напомню, функция ЛЕВСИМВ возвращает заданное количество символов слева направо, а ДЛСТР – считает количество символов в ячейке.
Чтобы убрать выделения без исправления, в этом же меню выберите «Удалить обводку неверных данных».
Понравилась статья? Считаете ее полезной? Поделитесь с другом – кликните на кнопках социальных сетей, или просто перешлите ссылку!
Я же отправляюсь писать новый важный пост, на этот раз про создание структуры таблицы. Не пропустите, особенно если Ваши таблицы большие и сложные. До встречи!
Excel проверка на пустую ячейку
- Останов – отменить внесение ошибочных данных, вывести заданное сообщение
- Предупреждение – программа сообщит, что значение не соответствует требованиям и уточнит, вносить ли его в ячейку
- Сообщение – примерно то же, что и предупреждение, но выглядит менее устрашающе
Примечание: Формулы в этом примере должны быть введены как формулы массива. Это означает, что сначала нужно нажать клавишу F2, а затем клавиши CTRL+SHIFT+ВВОД. В более ранних версиях Excel для Mac используйте клавиши +SHIFT+ВВОД.
Управление приоритетом правил условного форматирования. Если в excel
Подсчет количества вхождений значения — Служба поддержки Office
Предположим, что нужно определить, сколько раз встречается конкретный текст или число в диапазоне ячеек. Например:
если диапазон, например A2:D20, содержит числовые значения 5, 6, 7 и 6, то число 6 встречается два раза;
если столбец содержит значения «Шашков», «Туманов», «Туманов» и «Туманов», то значение «Туманов» встречается три раза.
Примечание: Снимки экрана в этой статье сделаны в программе Excel 2016. В зависимости от версии Excel экран может выглядеть по-другому, но функциональность будет такой же.
Подсчитать количество вхождений значения можно несколькими способами.
Подсчет количества вхождений отдельного значения с помощью функции СЧЁТЕСЛИ
Используйте функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз встречается определенное значение в диапазоне ячеек.
Дополнительные сведения см. в статье Функция СЧЁТЕСЛИ.
Функция СЧЁТЕСЛИМН аналогична функции СЧЁТЕСЛИ с одним важным исключением: СЧЁТЕСЛИМН позволяет применить критерии к ячейкам в нескольких диапазонах и подсчитывает число соответствий каждому критерию. С функцией СЧЁТЕСЛИМН можно использовать до 127 пар диапазонов и критериев.
Дополнительные сведения об использовании этой функции для подсчета вхождений в нескольких диапазонах и с несколькими условиями см. в статье Функция СЧЁТЕСЛИМН.
Подсчет количества вхождений на основе условий с помощью функций СЧЁТ и ЕСЛИ
Формулы в этом примере должны быть введены как формулы массива. Если эта книга открыта в приложении Excel для Windows или Excel 2016 для Mac и вы хотите изменить формулу или создать похожую, нажмите клавишу F2, а затем клавиши CTRL+SHIFT+ВВОД, чтобы формула возвращала нужные результаты. В более ранних версиях Excel для Mac используйте клавиши +SHIFT+ВВОД.
Чтобы эти примеры формул работали, вторым аргументом функции ЕСЛИ должно быть число.
Дополнительные сведения об этих функциях см. в статьях Функция СЧЁТ и Функция ЕСЛИ.
Подсчет количества вхождений нескольких текстовых и числовых значений с помощью функций СУММ и ЕСЛИ
В следующих примерах функции ЕСЛИ и СУММ используются вместе. Функция ЕСЛИ сначала проверяет значения в определенных ячейках, а затем, если возвращается значение ИСТИНА, функция СУММ складывает значения, удовлетворяющие условию.
Функция выше означает, что если диапазон C2:C7 содержит значения Шашков и Туманов, то функция СУММ должна отобразить сумму записей, в которых выполняется условие. Формула найдет в данном диапазоне три записи для «Шашков» и одну для «Туманов» и отобразит 4.
Функция выше означает, что если ячейка D2:D7 содержит значения меньше 9 000 ₽ или больше 19 000 ₽, то функция СУММ должна отобразить сумму всех записей, в которых выполняется условие. Формула найдет две записи D3 и D5 со значениями меньше 9 000 ₽, а затем D4 и D6 со значениями больше 19 000 ₽ и отобразит 4.
Функция выше означает, что если диапазон D2:D7 содержит счета для записи «Шашков» на сумму меньше 9 000 ₽, то функция СУММ должна отобразить сумму записей, в которых выполняется условие. Формула найдет ячейку C6, которая соответствует условию, и отобразит 1.
Примечание: Формулы в этом примере должны быть введены как формулы массива. Это означает, что сначала нужно нажать клавишу F2, а затем клавиши CTRL+SHIFT+ВВОД. В более ранних версиях Excel для Mac используйте клавиши +SHIFT+ВВОД.
Подсчет количества вхождений нескольких значений, с помощью сводной таблицы
В диалоговом окне «Создание сводной таблицы» установите переключатель Выбрать таблицу или диапазон, а затем — На новый лист и нажмите кнопку ОК.
Пустая сводная таблица будет создана на новом листе.
В области «Поля сводной таблицы» выполните одно из указанных ниже действий.
Имя поля Сумма_продаж_2 отобразится и в области «Сводная таблица», и в области «Значения».
На этом этапе область «Поля сводной таблицы» будет выглядеть так:
В области Значения щелкните стрелку раскрывающегося списка рядом с полем Сумма_продаж_2 и выберите пункт Параметры поля значений.
В диалоговом окне Параметры поля значений выполните указанные ниже действия.
В поле Пользовательское имя измените имя на Количество.
Сводная таблица отобразит количество записей для разделов «Гольф» и «Теннис» за кварталы 3 и 4, а также показатели продаж.
У вас есть вопрос об Excel?
Задайте его на форуме сообщества, посвященного Excel
Помогите нам улучшить Excel
У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.
См. также
Рекомендации, позволяющие избежать появления неработающих формул
Математические и тригонометрические функции (справочник)
Проверка ячейки на наличие в ней текста (без учета регистра)
Предположим, что вы хотите убедиться, что столбец содержит текст, не номера. Или perhapsyou необходимо найти все заказы, которые соответствуют определенным Продавец. Если у вас нет проблемой верхний или нижний регистр текста, существует несколько способов проверки, если ячейка содержит текст.
Для поиска текста можно также использовать фильтр. Дополнительные сведения см. в статье Фильтрация данных.
Поиск ячеек, содержащих текст
Выделите диапазон ячеек, среди которых требуется осуществить поиск.
Чтобы выполнить поиск по всему листу, щелкните любую ячейку.
На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить и нажмите кнопку Найти.
В поле Найти введите текст — или номера —, вам нужно найти. Или выберите из раскрывающегося списка Найти последнего поиска.
Примечание: В условиях поиска можно использовать подстановочные знаки.
Чтобы задать формат для поиска, нажмите кнопку Формат и внесите нужные изменения во всплывающем окне Найти формат.
Кнопка Параметры служит для задания более подробных условий поиска. Например, можно найти все ячейки, содержащие данных определенного типа, такого как формулы.
Для поиска на текущем листе или во всей книге можно выбрать в поле Искать вариант Лист или Книга.
Найти все списки каждого экземпляра элемента, который необходимо найти, и позволяет сделать активной ячейки, выбрав нужное вхождение. Можно сортировать результаты Найти все, щелкнув заголовок.
Примечание: Чтобы прекратить процесс поиска, нажмите клавишу ESC.
Проверка ячейки на наличие в ней любого текста
Для выполнения этой задачи используется функция ЕТЕКСТ .
Проверка соответствия содержимого ячейки определенному тексту
Для возвращения результатов для условия, которое можно указать с помощью функции Если .
Проверка соответствия части ячейки определенному тексту
Для выполнения этой задачи используются функции Если, Поиски ЕЧИСЛО .
Управление приоритетом правил условного форматирования
Создание, редактирование, удаление и просмотр всех правил условного форматирования в книге выполняются в диалоговом окне Диспетчер правил условного форматирования. (Для этого на вкладке Главная выберите команду Условное форматирование и выберите пункт Управление правилами.)
Когда к диапазону ячеек применяются несколько правил условного форматирования, приоритет обработки (сверху вниз) определяется порядком их расположения в этом диалоговом окне.
Ниже приведен пример с датами окончания срока действия для удостоверений личности. Необходимо изменить фон удостоверений, срок действия которых завершается через 60 дней, но еще не завершился, на желтый цвет, а цвет фона удостоверений с завершенным сроком действия — на красный.
В данном примере ячейки с идентификационными номерами сотрудников, у которых даты сертификации истекают через 60 дней, отформатированы желтым, а идентификационные номера сотрудников с истекшим сроком сертификации отформатированы красным (см. рис. ниже).
Правило, расположенное выше по списку, имеет более высокий приоритет, чем правило, расположенное ниже по списку. По умолчанию новые правила всегда добавляются в начало списка и поэтому обладают более высоким приоритетом, поэтому вам нужно следить за их порядком. Порядок правил можно изменить в диалоговом окне при помощи кнопок со стрелками Вверх и Вниз.
Ввод данных вручную в ячейки листа
Excel предоставляет несколько настроек для упрощения ручного ввода данных. Некоторые изменения влияют на все книги, некоторые — на весь лист, а другие влияют только на заданные ячейки.
Если использовать клавишу TAB для ввода данных в нескольких ячейках строки, а затем нажать клавишу ВВОД в конце этой строки, по умолчанию курсор переместится к началу следующей строки.
Нажатие клавиши ВВОД перемещает курсор на одну ячейку вниз, а нажатие клавиши TAB — на одну ячейку вправо. Направление перемещения для клавиши TAB изменить нельзя, но для клавиши ВВОД — можно. Изменение этого параметра влияет на весь лист, все другие открытые листы и книги, а также новые книги.
В Excel 2007 только: нажмите Кнопку Microsoft Office , а затем щелкните Параметры Excel.
В группе Дополнительно в разделе Параметры правки установите флажок Переход к другой ячейке после нажатия клавиши ВВОД и выберите нужное направление в списке Направление.
Щелкните ячейку, для которой требуется изменить ширину столбца.
На вкладке Главная в группе Ячейки нажмите кнопку Формат.
Выделите пункт Размер ячейки и выполните одно из следующих действий.
Чтобы подогнать размер ячейки под текст, выберите команду Автоподбор ширины столбца.
Чтобы увеличить ширину столбца, выберите команду Ширина и в поле Ширина столбца введите нужное значение.
Примечание: Также ширину столбца можно увеличить, изменив формат столбца или отдельной ячейки. Например, можно изменить формат даты, чтобы отображался только день и месяц (формат «дд.мм»), например 31.12, или представить число в экспоненциальном формате, например 4E+08.
Путем переноса текста в ячейке можно отобразить несколько строк текста. Перенос текста в ячейке не влияет на другие ячейки.
Щелкните ячейку, в которой требуется выполнить перенос текста.
На вкладке Главная в группе Выравнивание выберите пункт Переносить текст.
Дополнительные сведения о переносе текста см. в разделе Перенос текста в ячейке.
После ввода чисел в ячейку можно изменить формат их отображения.
На вкладке Главная в группе Число щелкните стрелку рядом с полем Числовой формат и выберите нужный формат.
Чтобы выбрать формат из списка доступных, нажмите кнопку Дополнительные числовые форматы и выберите нужный формат из списка Числовые форматы.
Для чисел, с которыми не требуется выполнять вычисления, например для номеров телефонов, можно выбрать текстовый формат, применив его к ячейкам перед вводом текста.
На вкладке Главная в группе Число щелкните стрелку рядом с полем Числовой формат и выберите пункт Текстовый.
Числа, которые были введены перед применением к ячейкам текстового формата, необходимо ввести заново в отформатированные ячейки. Чтобы ускорить повторный ввод чисел в текстовом формате, нажмите клавишу F2, а затем — клавишу ВВОД.
Изменились условия и тарифы доставки товаров по Минской области. Изменения в лучшую для покупателей сторону.
Если в excel. Проверка ячейки на наличие в ней текста (без учета регистра)
Эта формула означает: ЕСЛИ(в ячейке D3 ничего нет, вернуть текст «Пустая», в противном случае вернуть текст «Не пустая»). Вот пример распространенного способа использования знаков «», при котором формула не вычисляется, если зависимая ячейка пуста:
